package com.egao.orderB.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.egao.api.controller.ApiFileController;
import com.egao.common.core.Constants;
import com.egao.common.core.config.WebSocketServer;
import com.egao.common.core.damon.constant.UserConstant;
import com.egao.common.core.damon.results.PageResult2;
import com.egao.common.core.damon.utils.DateUtil;
import com.egao.common.core.damon.utils.*;
import com.egao.common.core.web.JsonResult;
import com.egao.common.system.entity.User;
import com.egao.common.system.service.UserService;
import com.egao.gps.dao.AllTempsDao;
import com.egao.gps.entity.AllTemps;
import com.egao.gps.entity.CarPosition;
import com.egao.gps.service.CarPositionService;
import com.egao.gps.utils.MapNavUtil;
import com.egao.gzh.entity.TempSend;
import com.egao.gzh.service.SettingGzh;
import com.egao.orderB.biz.*;
import com.egao.orderB.entity.*;
import com.egao.orderB.service.BillExamineService;
import com.egao.orderB.service.BillOceanApplyService;
import com.egao.orderB.service.BillService;
import com.egao.orderB.service.DriverMileageService;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

//import com.egao.orderB.mapper.BillMapper;
//import javax.transaction.Transactional;

/**
 * 订单管理服务实现类
 * @author : Doman
 * @date : 2020-05-28 14:12:19
 * @version ：1.0
 */
@Service
public class BillServiceImpl<main> /*extends ServiceImpl<BillMapper, Bill> */implements BillService {

    @Autowired
    private AddressBiz addressBiz;
    @Autowired
    private MaintainFeeBiz maintainFeeBiz;
	@Autowired
	private BillBiz billBiz;
	@Autowired
	private BillProcessBiz billProcessBiz;
	@Autowired
	private BillFeeBiz billFeeBiz;
	@Autowired
	private ApplyCustomsFeeBiz applyCustomsFeeBiz;
	@Autowired
	private BillOceanApplyBiz billOceanApplyBiz;
	@Autowired
	private CustomerBiz customerBiz;
    @Autowired
    private MaintainFeeAttachBiz maintainFeeAttachBiz;
    @Autowired
    private InvoiceNameBiz invoiceNameBiz;
//    @Autowired
//    private EmailService emailService;
    @Autowired
    private UserService userService;
    @Autowired
    private AddressFactoryBiz addressFactoryBiz;
    @Autowired
    private BillExamineService billExamineService;
    @Autowired
    private DriverBiz driverBiz;
    @Autowired
    private CarPositionService carPositionService;
    @Autowired
    private DriverMileageService driverMileageService;
    @Autowired
    private SettingsBiz settingsBiz;
    @Autowired
    private AllTempsDao allTempsDao;
    @Autowired
    private MessageBiz messageBiz;
    @Autowired
    private BillOceanApplyService billOceanApplyService;

    /**
    @Override
    public PageResult<Bill> listPage(PageParam<Bill> page) {
        List<Bill> records = baseMapper.listPage(page);
        return new PageResult<>(records, page.getTotal());
    }

    @Override
    public List<Bill> listAll(Map<String, Object> page) {
        return baseMapper.listAll(page);
    } */

    @Override
    public JsonResult list(PageResult2<T> page, Bill bill, User loginUser) throws Exception {
        JsonResult jr = billBiz.list(page,bill,loginUser);
        List<Bill> billList = (List<Bill>)jr.getData();
        if(billList != null && billList.size() > 0) {
            for(int i = 0; i < billList.size(); i ++) {
                Bill b = billList.get(i);
//                BillFee bf = new BillFee();
//                bf.setBillId(b.getId());
//                b.setBillFeeList(billFeeBiz.list(bf, loginUser));
                AddressFactory addressFactory = new AddressFactory();
                addressFactory.setBillId(b.getId());
                b.setAddressFactoryList(addressFactoryBiz.list(addressFactory, loginUser));

                /*List<BillFee> billFees = b.getBillFeeList();
                float calculate = 0;
                List<BillFeeVo> list = new ArrayList<>();
                BillFeeVo vo = new BillFeeVo();
                for (BillFee bf2 : billFees) {
                    if(Utils.isNotEmpty(bf2.getPrice())){
                        calculate = Float.parseFloat(bf2.getPrice());
                        vo = new BillFeeVo();
                        vo.setType(bf2.getType());
                        vo.setCurrency(bf2.getCurrency());
                        vo.setTotal(calculate+"");
                        vo.setTotalNoTax(calculate+"");
                        if(Utils.isNotEmpty(bf2.getExchangeRate())) {
                            calculate = Float.parseFloat(bf2.getPrice())*Float.parseFloat(bf2.getExchangeRate());
                            vo.setTotal(calculate+"");
                            vo.setTotalNoTax(calculate+"");
                            if(Utils.isNotEmpty(bf2.getInvoicestate())&& bf2.getInvoicestate().equals("1")) {
                                calculate = SszgUtils.getInvoiceNum(calculate,bf2.getDriverName());
                                vo.setTotalNoTax(calculate+"");
                            }
                        }
                        if (Utils.isNotEmpty(list)) {
                            boolean ret = true;
                            for (BillFeeVo vo2: list) {
                                if(Utils.isNotEmpty(vo2.getCurrency())) {
                                    if(vo2.getCurrency().equals(vo.getCurrency())&&vo2.getType().equals(vo.getType())) {
                                        vo2.setTotal((Float.parseFloat(vo2.getTotal())+Float.parseFloat(vo.getTotal()))+"");
                                        vo2.setTotalNoTax((Float.parseFloat(vo2.getTotalNoTax())+Float.parseFloat(vo.getTotalNoTax()))+"");
                                        ret = false;
                                        break;
                                    }
                                } else if(vo2.getType().equals(vo.getType())) {
                                    vo2.setTotal((Float.parseFloat(vo2.getTotal())+Float.parseFloat(vo.getTotal()))+"");
                                    vo2.setTotalNoTax((Float.parseFloat(vo2.getTotalNoTax())+Float.parseFloat(vo.getTotalNoTax()))+"");
                                    ret = false;
                                    break;
                                }
                            }
                            if (ret) {
                                list.add(vo);
                            }
                        } else {
                            list.add(vo);
                        }
                    }
                }
                b.setBillFeeVoList(list);
                if (Utils.isNotEmpty(list)) {
                    float s = 0,f = 0,ds = 0,df = 0;
                    for(BillFeeVo vo3: list) {
                        if (vo3.getType().equals("应收")) {
                            s += Float.parseFloat(vo3.getTotal());
                            ds += Float.parseFloat(vo3.getTotalNoTax());
                        }
                        if (vo3.getType().equals("应付")) {
                            f += Float.parseFloat(vo3.getTotal());
                            df += Float.parseFloat(vo3.getTotalNoTax());
                        }
                    }
                    b.setFeeTotalS(s+"");
                    b.setFeeTotalF(f+"");
                    b.setFeeTotalSDiscount(ds+"");
                    b.setFeeTotalFDiscount(df+"");

                } else {
                    b.setFeeTotalF("0");
                    b.setFeeTotalS("0");
                    b.setFeeTotalSDiscount("0");
                    b.setFeeTotalFDiscount("0");
                }

                BillProcess billProcess = new BillProcess();
                billProcess.setBillId(b.getId());
                b.setBillProcessList(billProcessBiz.list(billProcess));*/
            }
        }
//        emailService.sendTextEmail("甩手掌柜邮件标题", "甩手掌柜邮件内容", new String[]{"791680309@qq.com","2542387704@qq.com"});
        return jr;
    }

    @Override
    public JsonResult list2(PageResult2<T> page, Bill bill, User loginUser) throws Exception {
        JsonResult jr = billBiz.list(page,bill,loginUser);
        List<Bill> billList = (List<Bill>)jr.getData();
        if(billList != null && billList.size() > 0) {
            for(int i = 0; i < billList.size(); i ++) {
                Bill b = billList.get(i);
                BillFee bf = new BillFee();
                bf.setBillId(b.getId());
                b.setBillFeeList(billFeeBiz.list(bf, loginUser));
                AddressFactory addressFactory = new AddressFactory();
                addressFactory.setBillId(b.getId());
                b.setAddressFactoryList(addressFactoryBiz.list(addressFactory, loginUser));

                List<BillFee> billFees = b.getBillFeeList();
                float calculate = 0;
                List<BillFeeVo> list = new ArrayList<>();
                BillFeeVo vo = new BillFeeVo();
                for (BillFee bf2 : billFees) {
                    if(Utils.isNotEmpty(bf2.getPrice())){
                        calculate = Float.parseFloat(bf2.getPrice());
                        vo = new BillFeeVo();
                        vo.setType(bf2.getType());
                        vo.setCurrency(bf2.getCurrency());
                        vo.setTotal(calculate+"");
                        vo.setTotalNoTax(calculate+"");
                        if(Utils.isNotEmpty(bf2.getExchangeRate())) {
                            calculate = Float.parseFloat(bf2.getPrice())*Float.parseFloat(bf2.getExchangeRate());
                            vo.setTotal(calculate+"");
                            vo.setTotalNoTax(calculate+"");
                            if(Utils.isNotEmpty(bf2.getInvoicestate())&& bf2.getInvoicestate().equals("1")) {
                                calculate = SszgUtils.getInvoiceNum(calculate,bf2.getDriverName());
                                vo.setTotalNoTax(calculate+"");
                            }
                        }
                        if (Utils.isNotEmpty(list)) {
                            boolean ret = true;
                            for (BillFeeVo vo2: list) {
                                if(Utils.isNotEmpty(vo2.getCurrency())) {
                                    if(vo2.getCurrency().equals(vo.getCurrency())&&vo2.getType().equals(vo.getType())) {
                                        vo2.setTotal((Float.parseFloat(vo2.getTotal())+Float.parseFloat(vo.getTotal()))+"");
                                        vo2.setTotalNoTax((Float.parseFloat(vo2.getTotalNoTax())+Float.parseFloat(vo.getTotalNoTax()))+"");
                                        ret = false;
                                        break;
                                    }
                                } else if(vo2.getType().equals(vo.getType())) {
                                    vo2.setTotal((Float.parseFloat(vo2.getTotal())+Float.parseFloat(vo.getTotal()))+"");
                                    vo2.setTotalNoTax((Float.parseFloat(vo2.getTotalNoTax())+Float.parseFloat(vo.getTotalNoTax()))+"");
                                    ret = false;
                                    break;
                                }
                            }
                            if (ret) {
                                list.add(vo);
                            }
                        } else {
                            list.add(vo);
                        }
                    }
                }
                b.setBillFeeVoList(list);
                if (Utils.isNotEmpty(list)) {
                    float s = 0,f = 0,ds = 0,df = 0;
                    for(BillFeeVo vo3: list) {
                        if (vo3.getType().equals("应收")) {
                            s += Float.parseFloat(vo3.getTotal());
                            ds += Float.parseFloat(vo3.getTotalNoTax());
                        }
                        if (vo3.getType().equals("应付")) {
                            f += Float.parseFloat(vo3.getTotal());
                            df += Float.parseFloat(vo3.getTotalNoTax());
                        }
                    }
                    b.setFeeTotalS(s+"");
                    b.setFeeTotalF(f+"");
                    b.setFeeTotalSDiscount(ds+"");
                    b.setFeeTotalFDiscount(df+"");

                } else {
                    b.setFeeTotalF("0");
                    b.setFeeTotalS("0");
                    b.setFeeTotalSDiscount("0");
                    b.setFeeTotalFDiscount("0");
                }

                BillProcess billProcess = new BillProcess();
                billProcess.setBillId(b.getId());
                b.setBillProcessList(billProcessBiz.list(billProcess));
            }
        }
//        emailService.sendTextEmail("甩手掌柜邮件标题", "甩手掌柜邮件内容", new String[]{"791680309@qq.com","2542387704@qq.com"});
        return jr;
    }

    @Override
    public JsonResult listOpen(Bill bill, User loginUser) {
        Bill b = billBiz.getById(bill);
        BillFee bf = new BillFee();
        bf.setBillId(b.getId());
        b.setBillFeeList(billFeeBiz.list(bf, loginUser));
//        AddressFactory addressFactory = new AddressFactory();
//        addressFactory.setBillId(b.getId());
//        b.setAddressFactoryList(addressFactoryBiz.list(addressFactory, loginUser));

        List<BillFee> billFees = b.getBillFeeList();
        float calculate = 0;
        List<BillFeeVo> list = new ArrayList<>();
        BillFeeVo vo = new BillFeeVo();
        for (BillFee bf2 : billFees) {
            if(Utils.isNotEmpty(bf2.getPrice())){
                calculate = Float.parseFloat(bf2.getPrice());
                vo = new BillFeeVo();
                vo.setType(bf2.getType());
                vo.setCurrency(bf2.getCurrency());
                vo.setTotal(calculate+"");
                vo.setTotalNoTax(calculate+"");
                if(Utils.isNotEmpty(bf2.getExchangeRate())) {
                    calculate = Float.parseFloat(bf2.getPrice())*Float.parseFloat(bf2.getExchangeRate());
                    vo.setTotal(calculate+"");
                    vo.setTotalNoTax(calculate+"");
                    if(Utils.isNotEmpty(bf2.getInvoicestate())&& bf2.getInvoicestate().equals("1")) {
                        calculate = SszgUtils.getInvoiceNum(calculate,bf2.getDriverName());
                        vo.setTotalNoTax(calculate+"");
                    }
                }
                if (Utils.isNotEmpty(list)) {
                    boolean ret = true;
                    for (BillFeeVo vo2: list) {
                        if(Utils.isNotEmpty(vo2.getCurrency())) {
                            if(vo2.getCurrency().equals(vo.getCurrency())&&vo2.getType().equals(vo.getType())) {
                                vo2.setTotal((Float.parseFloat(vo2.getTotal())+Float.parseFloat(vo.getTotal()))+"");
                                vo2.setTotalNoTax((Float.parseFloat(vo2.getTotalNoTax())+Float.parseFloat(vo.getTotalNoTax()))+"");
                                ret = false;
                                break;
                            }
                        } else if(vo2.getType().equals(vo.getType())) {
                            vo2.setTotal((Float.parseFloat(vo2.getTotal())+Float.parseFloat(vo.getTotal()))+"");
                            vo2.setTotalNoTax((Float.parseFloat(vo2.getTotalNoTax())+Float.parseFloat(vo.getTotalNoTax()))+"");
                            ret = false;
                            break;
                        }
                    }
                    if (ret) {
                        list.add(vo);
                    }
                } else {
                    list.add(vo);
                }
            }
        }
        b.setBillFeeVoList(list);
        if (Utils.isNotEmpty(list)) {
            float s = 0,f = 0,ds = 0,df = 0;
            for(BillFeeVo vo3: list) {
                if (vo3.getType().equals("应收")) {
                    s += Float.parseFloat(vo3.getTotal());
                    ds += Float.parseFloat(vo3.getTotalNoTax());
                }
                if (vo3.getType().equals("应付")) {
                    f += Float.parseFloat(vo3.getTotal());
                    df += Float.parseFloat(vo3.getTotalNoTax());
                }
            }
            b.setFeeTotalS(s+"");
            b.setFeeTotalF(f+"");
            b.setFeeTotalSDiscount(ds+"");
            b.setFeeTotalFDiscount(df+"");

        } else {
            b.setFeeTotalF("0");
            b.setFeeTotalS("0");
            b.setFeeTotalSDiscount("0");
            b.setFeeTotalFDiscount("0");
        }

        BillProcess billProcess = new BillProcess();
        billProcess.setBillId(b.getId());
        b.setBillProcessList(billProcessBiz.list(billProcess));

        return JsonResult.ok().put("data", b);
    }

    @Override
    public JsonResult listDel(PageResult2<T> page, Bill bill, User loginUser) throws Exception {
        JsonResult jr = billBiz.listDel(page,bill,loginUser);
        return jr;
    }

    @Override
    public JsonResult financeList(PageResult2<T> page, Bill bill, User loginUser) throws Exception {
        bill.setFeeStateF("financeList");
        JsonResult jr = billBiz.list(page,bill,loginUser);
        List<Bill> billList = (List<Bill>)jr.getData();
        if(billList != null && billList.size() > 0) {
            for(int i = 0; i < billList.size(); i ++) {
                Bill b = billList.get(i);
                BillFee bf = new BillFee();
                bf.setBillId(b.getId());
                b.setBillFeeList(billFeeBiz.list(bf, loginUser));

                List<BillFee> billFees = b.getBillFeeList();
                float calculate = 0;
                List<BillFeeVo> list = new ArrayList<>();
                BillFeeVo vo = new BillFeeVo();
                for (BillFee bf2 : billFees) {
                    if(Utils.isNotEmpty(bf2.getPrice())){
                        calculate = Float.parseFloat(bf2.getPrice());
                        vo = new BillFeeVo();
                        vo.setType(bf2.getType());
                        vo.setCurrency(bf2.getCurrency());
                        vo.setTotal(calculate+"");
                        vo.setTotalNoTax(calculate+"");
                        if(Utils.isNotEmpty(bf2.getExchangeRate())) {
                            calculate = Float.parseFloat(bf2.getPrice())*Float.parseFloat(bf2.getExchangeRate());
                            vo.setTotal(calculate+"");
                            vo.setTotalNoTax(calculate+"");
                            if(Utils.isNotEmpty(bf2.getInvoicestate())&& bf2.getInvoicestate().equals("1")) {
                                calculate = SszgUtils.getInvoiceNum(calculate, bf2.getDriverName());
                                vo.setTotalNoTax(calculate+"");
                            }
                        }
                        if (Utils.isNotEmpty(list)) {
                            boolean ret = true;
                            for (BillFeeVo vo2: list) {
                                if(Utils.isNotEmpty(vo2.getCurrency())) {
                                    if(vo2.getCurrency().equals(vo.getCurrency())&&vo2.getType().equals(vo.getType())) {
                                        vo2.setTotal((Float.parseFloat(vo2.getTotal())+Float.parseFloat(vo.getTotal()))+"");
                                        vo2.setTotalNoTax((Float.parseFloat(vo2.getTotalNoTax())+Float.parseFloat(vo.getTotalNoTax()))+"");
                                        ret = false;
                                        break;
                                    }
                                } else {
                                    if(vo2.getType().equals(vo.getType())) {
                                        vo2.setTotal((Float.parseFloat(vo2.getTotal())+Float.parseFloat(vo.getTotal()))+"");
                                        vo2.setTotalNoTax((Float.parseFloat(vo2.getTotalNoTax())+Float.parseFloat(vo.getTotalNoTax()))+"");
                                        ret = false;
                                        break;
                                    }
                                }
                            }
                            if (ret) {
                                list.add(vo);
                            }
                        } else {
                            list.add(vo);
                        }
                    }
                }
                b.setBillFeeVoList(list);
                if (Utils.isNotEmpty(list)) {
                    float s = 0,f = 0,ds = 0,df = 0;
                    for(BillFeeVo vo3: list) {
                        if (vo3.getType().equals("应收")) {
                            s += Float.parseFloat(vo3.getTotal());
                            ds += Float.parseFloat(vo3.getTotalNoTax());
                        }
                        if (vo3.getType().equals("应付")) {
                            f += Float.parseFloat(vo3.getTotal());
                            df += Float.parseFloat(vo3.getTotalNoTax());
                        }
                    }
                    b.setFeeTotalS(s+"");
                    b.setFeeTotalF(f+"");
                    b.setFeeTotalSDiscount(ds+"");
                    b.setFeeTotalFDiscount(df+"");

                    b.setReceivable(s+"");
                    b.setPayable(f+"");
                    b.setProfit((s - f)+"");
                } else {
                    b.setReceivable("0");
                    b.setPayable("0");
                    b.setProfit("0");

                    b.setFeeTotalF("0");
                    b.setFeeTotalS("0");
                    b.setFeeTotalSDiscount("0");
                    b.setFeeTotalFDiscount("0");
                }
            }
        }
//        emailService.sendTextEmail("甩手掌柜邮件标题", "甩手掌柜邮件内容", new String[]{"791680309@qq.com","2542387704@qq.com"});
        return jr;
    }

    @Override
    public List<Bill> calendarBillNum(Bill bill, User loginUser) throws Exception {
        return billBiz.calendarBillNum(bill,loginUser);
    }

    @Override
    public List<Bill> list(Bill bill) {
        return billBiz.list(bill);
    }

    @Override
    public List<Bill> financeExportsList(Bill bill) {
        List<Bill> billList = billBiz.exportsList(bill);
        if(billList != null && billList.size() > 0) {
            for (int i = 0; i < billList.size(); i++) {
                Bill b = billList.get(i);
                BillFee bf = new BillFee();
                bf.setBillId(b.getId());
                b.setBillFeeList(billFeeBiz.exportList(bf, new User(1)));
                AddressFactory addressFactory = new AddressFactory();
                addressFactory.setBillId(b.getId());
                b.setAddressFactoryList(addressFactoryBiz.list(addressFactory, new User(1)));
            }
        }
        return billList;
    }

    @Override
    public List<Bill> financeExportsList2(Bill bill) {
        List<Bill> billList = billBiz.exportsList(bill);
        if(billList != null && billList.size() > 0) {
            for (int i = 0; i < billList.size(); i++) {
                Bill b = billList.get(i);
                BillFee bf = new BillFee();
                bf.setBillId(b.getId());
                bf.setOrganizationName("11");
                b.setBillFeeList(billFeeBiz.exportList2(bf, new User(1)));
                AddressFactory addressFactory = new AddressFactory();
                addressFactory.setBillId(b.getId());
                b.setAddressFactoryList(addressFactoryBiz.list(addressFactory, new User(1)));
            }
        }
        return billList;
    }

    @Override
    public List<Bill> financeExportsClassifyList(Bill bill) {
        List<Bill> billList = billBiz.exportsList(bill);
        if(billList != null && billList.size() > 0) {
            for (int i = 0; i < billList.size(); i++) {
                Bill b = billList.get(i);
                BillFee bf = new BillFee();
                bf.setBillId(b.getId());
                b.setBillFeeList(billFeeBiz.exportListClassifyList(bf, new User(1)));
                AddressFactory addressFactory = new AddressFactory();
                addressFactory.setBillId(b.getId());
                b.setAddressFactoryList(addressFactoryBiz.list(addressFactory, new User(1)));
            }
        }
        return billList;
    }

    @Override
    public List<Bill> exportsList(Bill bill) {
        List<Bill> billList = billBiz.exportsList(bill);
        if(billList != null && billList.size() > 0) {
            for (int i = 0; i < billList.size(); i++) {
                Bill b = billList.get(i);
                BillFee bf = new BillFee();
                if(StringUtil.isNotBlank(bill.getCustomer())){
                    bf.setInvoicename(b.getCustomer());
                } else if(StringUtil.isNotBlank(bill.getCarNo())) {
                    bf.setInvoicename(b.getCarNo());
                } else if(StringUtil.isNotBlank(bill.getCarTeam())) {
                    bf.setInvoicename(b.getCarTeam());
                }
                if (Utils.isNotEmpty(bill.getType())) {
                    bf.setType(bill.getType());
                }
                bf.setBillId(b.getId());
                List<BillFee> billFees = billFeeBiz.exportList(bf, new User(1));
                if (billFees == null) {
                    billFees = new ArrayList<BillFee>();
                }
                if (Utils.isNotEmpty(bill.getApplyId()) && bill.getApplyId().equals("billAndApply")) {
                    BillOceanApply billOceanApply = new BillOceanApply();
                    billOceanApply.setBillId(Integer.valueOf(b.getId()));
                    List<BillOceanApply> billOceanApplies = billOceanApplyBiz.list(billOceanApply, new User(1));
                    if (Utils.isNotEmpty(billOceanApplies)) {
                        Integer applyId = billOceanApplies.get(0).getApplyId();
                        if (Utils.isNotEmpty(applyId)) {
                            ApplyCustomsFee applyCustomsFee = new ApplyCustomsFee();
                            applyCustomsFee.setApplyId(applyId+"");
                            applyCustomsFee.setInvoicename(b.getCustomer());
                            List<ApplyCustomsFee> applyCustomsFees = applyCustomsFeeBiz.list(applyCustomsFee, new User(1));
                            if (Utils.isNotEmpty(applyCustomsFees)) {
                                for (ApplyCustomsFee applyCustomsFee1 : applyCustomsFees) {
                                    BillFee billFee = new BillFee();
                                    billFee.setType(applyCustomsFee1.getType());
                                    billFee.setFeeName(applyCustomsFee1.getFeeName());
                                    billFee.setPrice(applyCustomsFee1.getPrice());
                                    billFee.setFobject(applyCustomsFee1.getFobject());
                                    billFee.setFeeModules(applyCustomsFee1.getFeeModules());
                                    billFee.setInvoiceid(applyCustomsFee1.getInvoiceid());
                                    billFee.setInvoicenum(applyCustomsFee1.getInvoicenum());
                                    billFee.setInvoicename(applyCustomsFee1.getInvoicename());
                                    billFee.setDriverName(applyCustomsFee1.getDriverName());
                                    billFee.setInvoicestate(applyCustomsFee1.getInvoicestate());
                                    billFee.setCurrency(applyCustomsFee1.getCurrency());
                                    billFee.setExchangeRate(applyCustomsFee1.getExchangeRate());
                                    billFee.setSettlement(applyCustomsFee1.getSettlement()+"");
                                    billFee.setSettlementPrice(applyCustomsFee1.getSettlementPrice());
                                    billFees.add(billFee);
                                }
                            }
                        }
                    }

                }
                b.setBillFeeList(billFees);
                AddressFactory addressFactory = new AddressFactory();
                addressFactory.setBillId(b.getId());
                b.setAddressFactoryList(addressFactoryBiz.list(addressFactory, new User(1)));
            }
        }
        return billList;
    }

    @Override
    public List<Bill> findCarName(Bill bill) {
        return billBiz.findCarName(bill);
    }

    @Override
    public JsonResult list(Bill bill, User loginUser) {
        return JsonResult.ok().setData(billBiz.list(bill,loginUser));
    }

    @Override
    public JsonResult listNum(Bill bill, User loginUser) {
        return JsonResult.ok().put("num", billBiz.listNum(bill,loginUser));
    }

    @Override
    public JsonResult listDayNum(Bill bill, User loginUser) {
        return JsonResult.ok().put("nums", billBiz.listDayNum(bill,loginUser));
    }

    @Override
    public JsonResult listWeekNum(Bill bill, User loginUser) {
        List<BillWeekNum> list = billBiz.listWeekNum(bill, loginUser);
        if(Utils.isNotEmpty(bill.getDate1()) && Utils.isNotEmpty(bill.getDate2())) {
            Integer d1 = billBiz.getWeek(bill);
            bill.setDate1(bill.getDate2());
            Integer d2 = billBiz.getWeek(bill);
            for (int cc = d1;cc<=d2;cc++) {
                boolean ret = true;
                if(list!=null && list.size()>0) {
                    for (int i =0 ; i < list.size(); i ++) {
                        BillWeekNum wn = list.get(i);
                        if(Integer.valueOf(wn.getDate())==cc){
                            wn.setDate(cc+"");
                            ret = false;
                            break;
                        }
                    }
                }
                if(ret) {
                    BillWeekNum wn = new BillWeekNum();
                    wn.setDate(cc+"");
                    wn.setNum("0");
                    list.add(wn);
                }
            }
        }
        Collections.sort(list , (BillWeekNum b1, BillWeekNum b2) -> b1.getDate().compareTo(b2.getDate()));
        return JsonResult.ok().put("nums", list);
    }

    @Override
    public JsonResult listMonthNum(Bill bill, User loginUser) {
        return JsonResult.ok().put("nums", billBiz.listMonthNum(bill,loginUser));
    }

    @Override
    public JsonResult listMonthCustomerAndDriverNum(Bill bill, User loginUser) {
        return JsonResult.ok().put("nums", billBiz.listMonthCustomerAndDriverNum(bill,loginUser));
    }

    @Override
    public JsonResult listUnPayNum(Bill bill, User loginUser) {
        return JsonResult.ok().put("nums", billBiz.listUnPayNum(bill,loginUser));
    }

    @Override
    public JsonResult listDockNum(Bill bill, User loginUser) {
        if(bill.getDock().equals("month") || bill.getDock().equals("last month")
                || bill.getDock().equals("quarter") || bill.getDock().equals("last quarter")) {
            List<BillWeekNum> billWeekNums = billBiz.listDockNum(bill,loginUser);
            if(billWeekNums != null && billWeekNums.size() > 0) {
                for (BillWeekNum bwn : billWeekNums) {
                    MapNavUtil mapResult = new MapNavUtil(bwn.getLatitude()+","+bwn.getLongitude(),UserConstant.TX_MAP_KEY);
                    try {
                        bwn.setCarNo(mapResult.getTxAddress().getAddress().replaceAll("�", ""));
                    } catch (Exception e) {
                        bwn.setCarNo("");
                    }
                }
            }
            return JsonResult.ok().put("nums", billWeekNums);
        }
        return JsonResult.ok().put("nums", billBiz.listDockNum(bill,loginUser));
    }

    @Override
    public Bill getById(Bill bill) {
        return billBiz.getById(bill);
    }

    //复制订单
    @Override
    @Transactional
    public JsonResult copyBill(Bill bill, User loginUser) {
        if(Utils.isEmpty(bill.getId())){
            return JsonResult.error("请选着一条订单,操作失败!");
        }
        bill = billBiz.getById(bill);
        bill.setState("");
        billBiz.order(bill, loginUser);
        return JsonResult.ok("复制成功");
    }

    //货代客户自己的系统下单,  导入到我们的系统
    //状态:已下单,已做单,已接单,已提箱,已到厂,已离厂,已进港,发起费用清单,司机费用已审核,货代费用已审核,所有费用已审核,结单
    @Override
    @Transactional
    public JsonResult BillOrder(BillImport billImport, Bill bill, User loginUser) {
        if(Utils.isEmpty(billImport.getDock())||
                Utils.isEmpty(billImport.getAddressId())|| Utils.isEmpty(billImport.getAddressType())||
                Utils.isEmpty(billImport.getWeight())|| Utils.isEmpty(billImport.getBoxType())||
                Utils.isEmpty(billImport.getPackDate())||
                Utils.isEmpty(billImport.getVessel())|| Utils.isEmpty(billImport.getVoyage())||
                Utils.isEmpty(billImport.getBillOfLading())|| Utils.isEmpty(billImport.getFactoryAddress())||
                billImport.getAnnexFile().isEmpty()||billImport.getAnnexFile() ==null
                ){
            return JsonResult.error("请上传完整下单内容,操作失败!");
        }
        String driverFreight = "",price = "", province = "";
        if (billImport.getAddressType().equals("adrress")) {
            Address a = new Address();
            a.setId(billImport.getAddressId());
            a = addressBiz.getById(a);
            driverFreight = a.getDriverPrice();
            price = a.getPrice();
            province = a.getAddress();
        } else if (billImport.getAddressType().equals("maintain")) {
            MaintainFee m = new MaintainFee();
            m.setId(billImport.getAddressId());
            m = maintainFeeBiz.getById(m);
            driverFreight = m.getDriverPrice();
            price = m.getPrice();
            province = m.getAddress();
        }

        bill.setDriverFreight(driverFreight);
        bill.setPrice(price);
        bill.setProvince(province);
        bill.setDock(billImport.getDock());
        bill.setAddress(billImport.getAddress());
        bill.setWeight(billImport.getWeight());
        bill.setBoxType(billImport.getBoxType());
        bill.setDriverFreight(billImport.getDriverFreight());
        bill.setPackDate(billImport.getPackDate());
        bill.setVessel(billImport.getVessel());
        bill.setVoyage(billImport.getVoyage());
        bill.setBillOfLading(billImport.getBillOfLading());
        bill.setFactoryAddress(billImport.getFactoryAddress());
        bill.setFactoryPhone(billImport.getFactoryPhone());
        bill.setRemark(billImport.getRemark());

        Customer cus = customerBiz.getById(new Customer(bill.getCustomerId(),true));
        //司机,车队,货代
        if(loginUser.getType().equals("司机")||loginUser.getType().equals("车队")||loginUser.getType().equals("货代")){
            Integer offerLimitNum = billBiz.customerOfferTodayNum(bill);
            if(Utils.isNotEmpty(cus.getOfferLimitNum()) && Integer.valueOf(cus.getOfferLimitNum())<=offerLimitNum){
                return JsonResult.error("当日下单数量有限,如有需求请联系管理员帮忙下单!");
            }
        }
        if(Utils.isEmpty(cus.getContacts()) ||Utils.isEmpty(cus.getContactsPhone()) || Utils.isEmpty(cus.getSalesmanId())
                || Utils.isEmpty(cus.getOperaterId()) ){
            return JsonResult.error("请找甩手掌柜客服,让他们维护客户的联系人,业务员,操作!");
        }
        ApiFileController customerFileWTController = new ApiFileController();
        JsonResult urlRes = customerFileWTController.uploadRegisterPath(billImport.getAnnexFile(), "/customer/");
        bill.setAnnex(urlRes.get("url").toString());
        bill.setContacts(cus.getContacts());
        bill.setContactsPhone(cus.getContactsPhone());
        bill.setOperater(cus.getOperater());
        bill.setOperaterId(cus.getOperaterId());
        bill.setSalesman(cus.getSalesman());
        bill.setSalesmanId(cus.getSalesmanId());
        bill.setState("已下单");
        int key= billBiz.order(bill, loginUser);
        BillProcess bp = new BillProcess();
        bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
        bp.setBillId(key+"");
        bp.setState("已下单");
        billProcessBiz.save(bp, loginUser);
        return JsonResult.ok("下单成功");
    }

    //货代下单
    //状态:已下单,已做单,已接单,已提箱,已到厂,已离厂,已进港,发起费用清单,司机费用已审核,货代费用已审核,所有费用已审核,结单
    @Override
    @Transactional
    public JsonResult order(Bill bill, User loginUser) {
        if(Utils.isEmpty(bill.getCustomer())||Utils.isEmpty(bill.getCustomerId())||
                Utils.isEmpty(bill.getDock())||
                Utils.isEmpty(bill.getWeight())|| Utils.isEmpty(bill.getBoxType())||
                Utils.isEmpty(bill.getPackDate())||
                Utils.isEmpty(bill.getVessel())|| Utils.isEmpty(bill.getVoyage())||
                Utils.isEmpty(bill.getBillOfLading())||
                bill.getAddressFactoryList()==null
                ){
            return JsonResult.error("请填写下单内容,操作失败!");
        }
//        String factoryIds = "";
//        boolean index = false;
//        if(Utils.isNotEmpty(bill.getEfFactoryAddress())){
//            index = true;
//        }
//        if (Utils.isNotEmpty(bill.getAddressFactoryList())) {
//            if (index) {
//                factoryIds = ",";
//            }
//            for (AddressFactory a : bill.getAddressFactoryList()) {
//                if(a.getFtype().equals("address")) {
//                } else if(a.getFtype().equals("maintainFee")) {
//                    MaintainFee maintainFee = new MaintainFee();
//                    maintainFee.setId(a.getMaintainFeeId());
//                    maintainFee = maintainFeeBiz.getById(maintainFee);
//                    Address add = new Address();
//                    setAddress(maintainFee, add, bill.getCustomerId());
//                    int addressId = addressBiz.save(add,loginUser);
//                    a.setAddressId(addressId+"");
//                }
//                factoryIds += addressFactoryBiz.save(a, loginUser)+",";
//            }
//            factoryIds = factoryIds.substring(0, factoryIds.length()-1);
//            bill.setEfFactoryAddress(bill.getEfFactoryAddress()+factoryIds);
//        }

        Customer cus = customerBiz.getById(new Customer(bill.getCustomerId(),true));
        //司机,车队,货代
        if(loginUser.getType().equals("司机")||loginUser.getType().equals("车队")||loginUser.getType().equals("货代")){
            Integer offerLimitNum = billBiz.customerOfferTodayNum(bill);
            if(Utils.isNotEmpty(cus.getOfferLimitNum()) && Integer.valueOf(cus.getOfferLimitNum())<=offerLimitNum){
                return JsonResult.error("当日下单数量有限,如有需求请联系管理员帮忙下单!");
            }
        }
        if(Utils.isEmpty(cus.getContacts()) ||Utils.isEmpty(cus.getContactsPhone()) || Utils.isEmpty(cus.getSalesmanId())
                || Utils.isEmpty(cus.getOperaterId()) ){
            return JsonResult.error("请先维护客户的联系人,业务员,操作!");
        }
        bill.setContacts(cus.getContacts());
        bill.setContactsPhone(cus.getContactsPhone());
        bill.setOperater(cus.getOperater());
        bill.setOperaterId(cus.getOperaterId());
        bill.setSalesman(cus.getSalesman());
        bill.setSalesmanId(cus.getSalesmanId());
        bill.setState("已下单");
        int key= billBiz.order(bill, loginUser);
        bill.setBillId(key+"");

        for (AddressFactory a : bill.getAddressFactoryList()) {
            a.setBillId(key+"");
            addressFactoryBiz.save(a, loginUser);
        }
        bill.setFactoryAddressJson(JSONArray.fromObject(bill.getAddressFactoryList()).toString());
        billExamineService.save(bill, loginUser);

        BillProcess bp = new BillProcess();
        bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
        bp.setBillId(key+"");
        bp.setState("已下单");
        billProcessBiz.save(bp, loginUser);

        if(Utils.isNotEmpty(bill.getOceanId())) {
            BillOceanApply billOceanApply = new BillOceanApply();
            billOceanApply.setKeywords("ocean");
            billOceanApply.setOceanId(Integer.valueOf(bill.getOceanId()));
            billOceanApply.setBillId(key);
            billOceanApplyService.setTriAngle(billOceanApply, loginUser);
        }
        try {
//            WebSocketServer.sendInfo("{\"customer\":\""+bill.getCustomer()+"\",\"billOfLading\":\""+bill.getBillOfLading()+"\"}","order");
            WebSocketServer.sendInfo(bill.getCustomer()+"已下单","order");
        } catch (IOException e) {
            e.printStackTrace();
        }
        String phone = "";
        //【宁波甩手掌柜网络科技有限公司】您好,客户:{1}, 提单号:{2}已下单,请您登陆甩手掌柜尽快接单,感谢您一直以来对甩手掌柜的支持,如有一问请联系客服(0574)28820591
        if(bill.getOperaterId().equals("187")) {
            //林霞 187 13255844523
            phone= "13255844523";
        } if(bill.getOperaterId().equals("132")) {
            //陈逸 132 13250939710
            phone= "13250939710";
        }
        //郑诚18668575026
        phone += ",18668575026";
        if (bill.getDock().equals("温州港")) {
            //王金轮 819 13968907228
            phone += ",13968907228";
        }
        if (bill.getSalesmanId().equals("78")) {
            phone += ",13186850898";
        }

//        User user = userService.getUserById(new User(Integer.valueOf(bill.getSalesmanId())));
//        if (phone.indexOf(user.getPhone()) == -1) {
//            phone += ","+user.getPhone();
//        }

        PhoneUtils.sendMsg(phone, "961275",
                    new String[]{bill.getCustomer(),bill.getBillOfLading()+", 装箱时间:"+bill.getPackDate()}, 1);
        return JsonResult.ok("下单成功");
    }


    public void setAddress(MaintainFee m, Address a, String customerId){
        a.setCustomerId(customerId);
        a.setCity(m.getCity());
        a.setDistrict(m.getDistrict());
        a.setTown(m.getTown());
        a.setAddress(m.getAddress());
        a.setDock(m.getDock());
        a.setBoxType(m.getBoxType());
        a.setState("已审核");
    }

    //平台接单
    @Override
    @Transactional
    public JsonResult platReceive(Bill bill, User loginUser) {
//        if(loginUser.getRoleIds().contains(UserConstant.DISPATCH_ROLE_ID)){
//            return JsonResult.error("调度师傅无法接单,操作失败!");    }
        if(Utils.isEmpty(bill.getId())){
            return JsonResult.error("请选择货代,操作失败!");
        }
        if (StrUtil.hasBlank(bill.getId())) {
            return JsonResult.error("请完整填写修改内容,操作失败!");
        }
        Bill bill2 = billBiz.getById(bill);
        if(loginUser.getUserId()!=UserConstant.ADMIN_ID) {
            if(!bill2.getState().equals("已下单")) {
                return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
            }
        }
        if (Utils.isNotEmpty(bill.getAddressFactoryList())) {
            for (AddressFactory a : bill.getAddressFactoryList()) {
                if(Utils.isEmpty(a.getLongitude()) || a.getLongitude().trim().equals("")){
                    return JsonResult.error("经纬度不能为空,操作失败!");
                }
                if(Utils.isEmpty(a.getLatitude()) || a.getLatitude().trim().equals("")){
                    return JsonResult.error("经纬度不能为空,操作失败!");
                }
                if(!Utils.isNotEmpty(a.getBillId())){
                    return JsonResult.error("订单id不能为空!");
                }
            }
        }

//        String factoryIds = "";
//        boolean index = false;
//        if(Utils.isNotEmpty(bill.getEfFactoryAddress())){
//            index = true;
//        }
//        if (Utils.isNotEmpty(bill.getAddressFactoryList())) {
//            if (index) {
//                factoryIds = ",";
//            }
//            for (AddressFactory a : bill.getAddressFactoryList()) {
//                if(a.getFtype().equals("address")) {
//                } else if(a.getFtype().equals("maintainFee")) {
//                    MaintainFee maintainFee = new MaintainFee();
//                    maintainFee.setId(a.getMaintainFeeId());
//                    maintainFee = maintainFeeBiz.getById(maintainFee);
//                    Address add = new Address();
//                    setAddress(maintainFee, add, bill.getCustomerId());
//                    int addressId = addressBiz.save(add,loginUser);
//                    a.setAddressId(addressId+"");
//                }
//                factoryIds += addressFactoryBiz.save(a, loginUser)+",";
//            }
//            factoryIds = factoryIds.substring(0, factoryIds.length()-1);
//            bill.setEfFactoryAddress(bill.getEfFactoryAddress()+factoryIds);
//        }


        for (AddressFactory a : bill.getAddressFactoryList()) {
            if(Utils.isNotEmpty(a.getId())) {
                addressFactoryBiz.update(a, loginUser);
            } else {
                addressFactoryBiz.save(a, loginUser);
            }
        }

        bill.setState("平台已接单");
        if (billBiz.platReceive_update(bill, loginUser)) {
            messageBiz.save(new Message("提单号:"+bill.getBillOfLading() + " 平台已接单", Integer.valueOf(bill2.getCustomerId()), "billState", bill.getBillOfLading()), loginUser);
            bill.setBillId(bill.getId());
            bill.setFactoryAddressJson(JSONArray.fromObject(bill.getAddressFactoryList()).toString());
            billExamineService.save(bill, loginUser);
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("平台已接单");
            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("平台接单成功");
        }
        return JsonResult.error("平台接单失败");
    }

    //指派车队接单
    @Override
    @Transactional
    public JsonResult carTeam(Bill bill, User loginUser) {
        if(Utils.isEmpty(bill.getId())){
            return JsonResult.error("请选择货代,操作失败!");
        }
        Bill bill2 = billBiz.getById(bill);
        if(loginUser.getUserId()!=UserConstant.ADMIN_ID) {
            if(!bill2.getState().equals("平台已接单") &&!bill2.getState().equals("已指派车队")
                    &&!bill2.getState().equals("已做单")) {
                return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
            }
        }
        if(Utils.isEmpty(bill.getCarTeam()) || Utils.isEmpty(bill.getCarTeamId())){
            return JsonResult.error("请填写车队信息,操作失败!");
        }
        bill.setState("已指派车队");
        if (billBiz.carTeam(bill, loginUser)) {
            bill.setBillId(bill.getId());
            billExamineService.save(bill, loginUser);
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("已指派车队");
            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("指派车队成功");
        }
        return JsonResult.error("指派车队失败");
    }

    //做单
    @Override
    @Transactional
    public JsonResult make(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
        if(loginUser.getUserId()!=UserConstant.ADMIN_ID) {
            if(!bill2.getState().equals("平台已接单")&&!bill2.getState().equals("已指派车队")
                    &&!bill2.getState().equals("已做单")/*&&!bill2.getState().equals("已提箱")
                    &&!bill2.getState().equals("已到厂")&&!bill2.getState().equals("已离厂")
                    &&!bill2.getState().equals("已进港")*/
            ) {
                return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
            }
        }

        if(Utils.isEmpty(bill.getDriver())){
            return JsonResult.error("请填写司机信息,操作失败!");
        }
        com.egao.orderB.entity.Driver driver = new com.egao.orderB.entity.Driver();
        driver.setId(bill.getDriverId());
        driver = driverBiz.getById(driver);
        int expired = 7;
        if (driver.getType().equals("B")) {
            if(StringUtil.isBlank(driver.getWhiteCardDate(),driver.getVehicleLicenseDate()
                    ,driver.getDriveLicenseDate(),driver.getOperationLicenseDate()
                    ,driver.getInsuranceLicenseDate())) {
                if(DateUtil.pastDays(DateUtil.parseDate(driver.getWhiteCardDate()))<expired) {
                    return JsonResult.error("司机"+ driver.getName()+"白卡号日期过期!,操作失败!");
                }
                if(DateUtil.pastDays(DateUtil.parseDate(driver.getVehicleLicenseDate()))<expired) {
                    return JsonResult.error("司机"+ driver.getName()+"行驶证日期过期!,操作失败!");
                }
                if(DateUtil.pastDays(DateUtil.parseDate(driver.getDriveLicenseDate()))<expired) {
                    return JsonResult.error("司机"+ driver.getName()+"驾驶证日期过期!,操作失败!");
                }
                if(DateUtil.pastDays(DateUtil.parseDate(driver.getOperationLicenseDate()))<expired) {
                    return JsonResult.error("司机"+ driver.getName()+"车辆营运证日期过期!,操作失败!");
                }
                if(DateUtil.pastDays(DateUtil.parseDate(driver.getInsuranceLicenseDate()))<expired) {
                    return JsonResult.error("司机"+ driver.getName()+"保险日期过期!,操作失败!");
                }
            } else {
                return JsonResult.error("请填写B司机证件信息,操作失败!");
            }
        }

        bill.setState("已做单");

        if (billBiz.make(bill, loginUser)) {
            messageBiz.save(new Message("提单号:"+bill2.getBillOfLading()+"已派司机:"+bill.getCarNo()+" "+bill.getDriver(),
                    Integer.valueOf(bill2.getCustomerId()), "billState", bill2.getBillOfLading()), loginUser);
            bill.setBillId(bill.getId());
            billExamineService.save(bill, loginUser);
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("已做单");
            billProcessBiz.save(bp, loginUser);
            //602245【宁波甩手掌柜网络科技有限公司】您好,提单号:{1}已指派给您,请您登陆甩手掌柜小程序,尽快接单,感谢您一直以来对甩手掌柜的支持,如有一问请联系客服(0574)28820591
//            PhoneUtils.sendMsg(bill.getDriverPhone(), "602245", new String[]{bill2.getBillOfLading()},1);
            int message = 0;
            try {
                Customer customer2 = customerBiz.getByName(new Customer(bill2.getCustomer())).get(0);
                message = Integer.valueOf(customer2.getMessage());
            } catch (Exception e) {
            }
            String phone = "";
            //林霞
            if(bill2.getOperaterId().equals("187")) {
                phone= "13255844523";
                //陈逸
            } else if(bill2.getOperaterId().equals("132")) {
                phone= "13250939710";
            }
            //【宁波甩手掌柜网络科技有限公司】您好,提单号:{1}已被甩手掌柜成功接单,司机师傅信息:{2},感谢您一直以来对甩手掌柜的支持,如有一问请联系客服(0574)28820591
//            PhoneUtils.sendMsg(bill2.getContactsPhone(), "597088",
            PhoneUtils.sendMsg(phone, "597088",
                    new String[]{bill2.getBillOfLading(),bill2.getCarNo()+"、"
                            +bill.getDriver()+"、"+bill.getDriverPhone()},message);
            try {
                WebSocketServer.sendInfo("客户:"+bill2.getCustomer()+", 提单号:"+bill2.getBillOfLading()+"已派司机:"+bill.getCarNo()+" "+bill.getDriver(),"order");
            } catch (IOException e) {
                e.printStackTrace();
            }
            AllTemps allTemps = new AllTemps();
            allTemps.setBillId(bill2.getId());
            allTemps.setCreateDate(DateUtil.formatDate(DateUtil.addDatePlusMin(DateUtil.parseDate(DateUtil.getCurrentDate()), 10)));
            allTemps.setType("sendDriver");
            AllTemps allTemps1 = allTempsDao.findByBillId(allTemps);
            if(allTemps1 != null) {
                allTempsDao.update(allTemps);
            } else {
                allTempsDao.save(allTemps);
            }
            return JsonResult.ok("做单成功");
        }
        return JsonResult.error("做单失败");
    }


    //测试    派司机后提醒客户司机已派  deleted
//    @Scheduled(cron="50 08 15 * * ?")
    public void customerSendGzh22() {
        Bill bill = null;

        bill = new Bill();
        bill.setId("5546");
        bill = billBiz.getById(bill);
        Customer customer = new Customer();
        customer.setId(bill.getCustomerId());
        customer = customerBiz.getById(customer);

        SettingGzh cm = new SettingGzh();
        TempSend tempSend2 = new TempSend();
        tempSend2.setTouser("owHBe6Egyt62JFzIEKHXPy2ZGoLo");
        tempSend2.setTemplateId("NmUIzARvtGDHaxf8PpXjMpU3H-Qy_5ZTj7St51Wd56s");
        tempSend2.setFirst("运单提单号:"+bill.getBillOfLading()+", 已派司机:"+bill.getCarNo()+", "+bill.getDriver());
        tempSend2.setKeyword1(DateUtil.getCurrentDate());
        tempSend2.setKeyword2("");
        tempSend2.setRemark("");
        List<Bill> billList = new ArrayList<>();
        billList.add(bill);
        tempSend2.setUrl("https://gzhsy.sszgjzx.com/orderdetails?selected="+ JSONObject.fromObject(bill));
        System.out.println("https://gzhsy.sszgjzx.com/orderdetails?selected="+ JSONObject.fromObject(bill));
        System.out.println("https://gzhsy.sszgjzx.com/orderdetails?selected="+ JSONArray.fromObject(billList));
        cm.setPrivateTemplate(tempSend2);
//                            System.out.println("运单提单号:"+bill.getBillOfLading()+", 已派司机:"+bill.getCarNo()+", "+bill.getDriver());


    }


    //派司机后提醒客户司机已派
//    @Scheduled(cron="0 */5 * * * ?")
    public void customerSendGzh() {
        AllTemps allTemps = new AllTemps();
        allTemps.setType("sendDriver");
        List<AllTemps> allTempsList = allTempsDao.list(allTemps);
        Bill bill = null;
        if(allTempsList != null && allTempsList.size() > 0) {
            for (AllTemps allTemps1: allTempsList) {
                if (DateUtil.compareToDate(DateUtil.parseDate(allTemps1.getCreateDate()), new Date()) < 1) {
                    bill = new Bill();
                    bill.setId(allTemps1.getBillId());
                    bill = billBiz.getById(bill);

                    List<Integer> userIds = new ArrayList<>();
                    userIds.add(Integer.valueOf(bill.getSalesmanId()));
                    userIds.add(Integer.valueOf(bill.getOperaterId()));
                    List<User> userList = userService.getGzhIdsByUserId(new User(userIds));


                    Customer customer = new Customer();
                    customer.setId(bill.getCustomerId());
                    customer = customerBiz.getById(customer);
                    User user = new User();
                    if(customer.getGzhSend().equals("1")){
                        user.setGzhId("not null");
                        user.setUsername(customer.getAdminUser());
                        user = userService.getUserByUsername(user);

                    }
                    if (user != null && Utils.isNotEmpty(user.getGzhId())) {
                        userList.add(user);
                    }
                    for (User u: userList) {
                        if(Utils.isNotEmpty(u.getGzhId())) {
                            SettingGzh cm = new SettingGzh();
                            TempSend tempSend2 = new TempSend();
                            tempSend2.setTouser(u.getGzhId());
                            tempSend2.setTemplateId("NmUIzARvtGDHaxf8PpXjMpU3H-Qy_5ZTj7St51Wd56s");
                            tempSend2.setFirst("客户:" + bill.getCustomer() + ", 提单号:" + bill.getBillOfLading() + ", 已派司机:车牌:" + bill.getCarNo() + ", 司机:" + bill.getDriver() + ", 电话:" + bill.getDriverPhone());
                            tempSend2.setKeyword1(DateUtil.getCurrentDate());
                            tempSend2.setKeyword2("");
                            tempSend2.setRemark("状态:已派司机");
                            cm.setPrivateTemplate(tempSend2);
                        }
                    }
                    AllTemps allTemps2 = new AllTemps();
                    allTemps2.setBillId(bill.getId());
                    allTemps2.setType("sendDriver");
                    allTempsDao.removeByBillId(allTemps2);
                }

            }
        }
    }

    //落箱 预提的柜子每天都发一条短信
//    @Scheduled(cron="0 0 10 * * ?")
//    public void sendDropBoxAnd() {
//        if (JKXStartupRunner.runwithmodel2.equals("prod")) {
//            Bill bill = new Bill();
//            bill.setWithholding("3");//表示 落箱或者预提
//            List<Bill> list = billBiz.list(bill, new User(1));
//            for (Bill bill2 : list) {
//                List<Integer> userIds = new ArrayList<>();
//                userIds.add(Integer.valueOf(bill.getSalesmanId()));
//                List<User> userList = userService.getGzhIdsByUserId(new User(userIds));
//                for (User u: userList) {
//                    PhoneUtils.sendMsg(MD5Utils.convertMD5(u.getPhone()), "1088738",
//                            new String[]{bill2.getBillOfLading()}, 1);
//                }
//            }
//        }
//    }

    //提醒客户已传箱封号
    @Scheduled(cron="0 */3 * * * ?")
    public void customerBoxNumSendGzh() {
        AllTemps allTemps = new AllTemps();
        allTemps.setType("boxNum");
        List<AllTemps> allTempsList = allTempsDao.list(allTemps);
        Bill bill = null;
        if(allTempsList != null && allTempsList.size() > 0) {
            for (AllTemps allTemps1: allTempsList) {
                if (DateUtil.compareToDate(DateUtil.parseDate(allTemps1.getCreateDate()), new Date()) < 1) {
                    bill = new Bill();
                    bill.setId(allTemps1.getBillId());
                    bill = billBiz.getById(bill);
                    Customer customer = new Customer();
                    customer.setId(bill.getCustomerId());
                    customer = customerBiz.getById(customer);
                    if(customer.getGzhSend().equals("1")){
                        User user = new User();
                        user.setGzhId("not null");
                        user.setUsername(customer.getAdminUser());
                        user = userService.getUserByUsername(user);
                        if (user != null && Utils.isNotEmpty(user.getGzhId())) {
                            SettingGzh cm = new SettingGzh();
                            TempSend tempSend2 = new TempSend();
                            tempSend2.setTouser(user.getGzhId());
                            tempSend2.setTemplateId("NmUIzARvtGDHaxf8PpXjMpU3H-Qy_5ZTj7St51Wd56s");
                            tempSend2.setFirst("运单提单号:"+bill.getBillOfLading()+", 司机:"+bill.getDriver()
                                            +",船名航次:"+bill.getVessel()+"/"+bill.getVoyage());
                            tempSend2.setKeyword1(DateUtil.getCurrentDate());
                            tempSend2.setKeyword2("");
                            tempSend2.setRemark("");
                            tempSend2.setUrl("");
                            cm.setPrivateTemplate(tempSend2);
                        }
                    }
                    AllTemps allTemps2 = new AllTemps();
                    allTemps2.setBillId(bill.getId());
                    allTemps2.setType("boxNum");
                    allTempsDao.removeByBillId(allTemps2);
                }

            }
        }
    }

    //提箱
    @Override
    @Transactional
    public JsonResult upBox_substitute(Bill bill, User loginUser) {
        if(StringUtil.isBlank(bill.getBoxNumPic(),bill.getSealnoPic())
        ){
            return JsonResult.error("请填上传箱封号信息,操作失败!");
        }
        Bill bill2 = billBiz.getById(bill);
        if (billBiz.upBox_substitute(bill, loginUser)) {
            AllTemps allTemps = new AllTemps();
            allTemps.setBillId(bill.getId());
            allTemps.setCreateDate(DateUtil.formatDate(DateUtil.addDatePlusMin(DateUtil.parseDate(DateUtil.getCurrentDate()), 5)));
            allTemps.setType("boxNum");
            AllTemps allTemps1 = allTempsDao.findByBillId(allTemps);
            if(allTemps1 != null) {
                allTempsDao.update(allTemps);
            } else {
                allTempsDao.save(allTemps);
            }
            try {
                WebSocketServer.sendInfo("客户:"+bill2.getCustomer()+", 提单号:"+bill2.getBillOfLading()+",派司机:"+bill2.getCarNo()+" "+bill2.getDriver()+" 提箱信息补录成功","order");
                messageBiz.save(new Message("提单号:"+bill2.getBillOfLading()+"  箱封号信息补录成功",
                        Integer.valueOf(bill2.getCustomerId()), "billState", bill2.getBillOfLading()), loginUser);
            } catch (IOException e) {
                e.printStackTrace();
            }
            return JsonResult.ok("提箱信息补录成功");
        }
        return JsonResult.error("提箱信息补录失败");
    }

    //提箱
    @Override
    @Transactional
    public JsonResult upBox_gps(Bill bill, User loginUser) {
        String remark = bill.getRemark();
        Bill bill2 = billBiz.getById(bill);
        bill.setState("已提箱");
        if (billBiz.upBox_gps(bill, loginUser)) {
            messageBiz.save(new Message("提单号:"+bill2.getBillOfLading()+"  司机已提箱",
                    Integer.valueOf(bill2.getCustomerId()), "billState", bill2.getBillOfLading()), loginUser);
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("已提箱");
            bp.setRemark(remark);
            billProcessBiz.save(bp, loginUser);
            //624616【宁波甩手掌柜网络科技有限公司】您好,提单号:{1},司机师傅信息:{2},感谢您一直以来对甩手掌柜的支持,如有一问请联系客服(0574)28820591
//            int message = 0;
//            try {
//                Customer customer2 = customerBiz.getByName(new Customer(bill2.getCustomer())).get(0);
//                message = Integer.valueOf(customer2.getMessage());
//            } catch (Exception e) {
//            }
//            PhoneUtils.sendMsg(bill2.getContactsPhone(), "624616",
//                    new String[]{bill2.getBillOfLading(),bill2.getCarNo()+"、"+bill2.getDriver()
//                            +"、"+bill2.getDriverPhone()+" 的运单已提箱"}, message);

            List<Integer> userIds = new ArrayList<>();
            userIds.add(Integer.valueOf(bill2.getSalesmanId()));
            userIds.add(Integer.valueOf(bill2.getOperaterId()));
            List<User> userList = userService.getGzhIdsByUserId(new User(userIds));

//            Customer customer = new Customer();
//            customer.setId(bill2.getCustomerId());
//            customer = customerBiz.getById(customer);
//            if(customer.getGzhSend().equals("1")) {
//                User u2 = new User();
//                u2.setUsername(customer.getAdminUser());
//                u2 = userService.getUserByUsername(u2);
//                if(Utils.isNotEmpty(u2.getGzhId())){
//                    userList.add(u2);
//                }
//            }
            for (User u: userList) {
                if(Utils.isNotEmpty(u.getGzhId())) {
                    SettingGzh cm = new SettingGzh();
                    TempSend tempSend2 = new TempSend();
                    tempSend2.setTouser(u.getGzhId());
                    tempSend2.setTemplateId("NmUIzARvtGDHaxf8PpXjMpU3H-Qy_5ZTj7St51Wd56s");
                    tempSend2.setFirst("客户:" + bill2.getCustomer() + " 提单号:" + bill2.getBillOfLading() + " 车牌:" + bill2.getCarNo() + " 司机:" + bill2.getDriver() + " 电话:" + bill2.getDriverPhone());
                    tempSend2.setKeyword1(DateUtil.getCurrentDate());
                    tempSend2.setKeyword2(remark);
                    tempSend2.setRemark("状态:已提箱");
                    cm.setPrivateTemplate(tempSend2);
                }
            }
            return JsonResult.ok("提箱成功");
        }
        return JsonResult.error("提箱失败");
    }

    //提箱
    @Override
    @Transactional
    public JsonResult upBox(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
//        if(!bill2.getState().equals("已做单")&&!bill2.getState().equals("司机已接单")) {
//            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
//        }
        if(Utils.isEmpty(bill.getBoxNum())||Utils.isEmpty(bill.getBoxNumPic())||
            Utils.isEmpty(bill.getSealno())||Utils.isEmpty(bill.getSealnoPic())
            ){
            return JsonResult.error("请填写提箱信息,操作失败!");
        }
        bill.setState("已提箱");
        if (billBiz.upBox(bill, loginUser)) {
            messageBiz.save(new Message("提单号:"+bill2.getBillOfLading()+"  司机已提箱",
                    Integer.valueOf(bill2.getCustomerId()), "billState", bill2.getBillOfLading()), loginUser);
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("已提箱");
            billProcessBiz.save(bp, loginUser);
            //624616【宁波甩手掌柜网络科技有限公司】您好,提单号:{1},司机师傅信息:{2},感谢您一直以来对甩手掌柜的支持,如有一问请联系客服(0574)28820591
            int message = 0;
            try {
                Customer customer2 = customerBiz.getByName(new Customer(bill2.getCustomer())).get(0);
                message = Integer.valueOf(customer2.getMessage());
            } catch (Exception e) {
            }
            PhoneUtils.sendMsg(bill2.getContactsPhone(), "624616",
                    new String[]{bill2.getBillOfLading(),bill2.getCarNo()+"、"+bill2.getDriver()
                            +"、"+bill2.getDriverPhone()+" 的运单已提箱"}, message);
            return JsonResult.ok("提箱成功");
        }
        return JsonResult.error("提箱失败");
    }

    //到厂
    @Override
    @Transactional
    public JsonResult arrivedFactory(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
//        if(!bill2.getState().equals("已提箱")) {
//            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
//        }
//        if(Utils.isEmpty(bill.getArrivedFactoryLatitude())||Utils.isEmpty(bill.getArrivedFactoryLongitude())||
//                Utils.isEmpty(bill.getArrivedFactoryDate())
//            ){
//            return JsonResult.error("请填写到厂信息,操作失败!");
//        }
        String remark = bill.getRemark();
        bill.setState("已到厂");
        if (billBiz.arrivedFactory(bill, loginUser)) {
            messageBiz.save(new Message("提单号:"+bill2.getBillOfLading()+"  司机已到场",
                    Integer.valueOf(bill2.getCustomerId()), "billState", bill2.getBillOfLading()), loginUser);
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("已到厂");
            bp.setRemark(remark);
            billProcessBiz.save(bp, loginUser);

            List<Integer> userIds = new ArrayList<>();
            userIds.add(Integer.valueOf(bill2.getSalesmanId()));
            userIds.add(Integer.valueOf(bill2.getOperaterId()));
            List<User> userList = userService.getGzhIdsByUserId(new User(userIds));

            Customer customer = new Customer();
            customer.setId(bill2.getCustomerId());
            customer = customerBiz.getById(customer);
            if(customer.getGzhSend().equals("1")) {
                User u2 = new User();
                u2.setUsername(customer.getAdminUser());
                u2 = userService.getUserByUsername(u2);
                if(Utils.isNotEmpty(u2.getGzhId())){
                    userList.add(u2);
                }
            }

            for (User u: userList) {
                SettingGzh cm = new SettingGzh();
                TempSend tempSend2 = new TempSend();
                tempSend2.setTouser(u.getGzhId());
                tempSend2.setTemplateId("NmUIzARvtGDHaxf8PpXjMpU3H-Qy_5ZTj7St51Wd56s");
                tempSend2.setFirst("客户:"+bill2.getCustomer()+" 提单号:"+bill2.getBillOfLading()+ " 车牌:"+bill2.getCarNo()+" 司机:"+bill2.getDriver() + " 电话:" + bill2.getDriverPhone());
                tempSend2.setKeyword1(DateUtil.getCurrentDate());
                tempSend2.setKeyword2(remark);
                tempSend2.setRemark("状态:已到厂");
                cm.setPrivateTemplate(tempSend2);
            }

            return JsonResult.ok("到厂成功");
        }
        return JsonResult.error("到厂失败");
    }

    //离厂
    @Override
    @Transactional
    public JsonResult leaveFactory(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
//        if(!bill2.getState().equals("已到厂")) {
//            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
//        }
//        if(Utils.isEmpty(bill.getLeavedFactoryDate())||Utils.isEmpty(bill.getLeavedFactoryLatitude())||
//                Utils.isEmpty(bill.getLeavedFactoryLongitude())
//        ){
//            return JsonResult.error("请填写离厂信息,操作失败!");
//        }
        String remark = bill.getRemark();
        bill.setState("已离厂");
        if (billBiz.leaveFactory(bill, loginUser)) {
            messageBiz.save(new Message("提单号:"+bill2.getBillOfLading()+"  司机已离厂",
                    Integer.valueOf(bill2.getCustomerId()), "billState", bill2.getBillOfLading()), loginUser);
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("已离厂");
            bp.setRemark(remark);
            billProcessBiz.save(bp, loginUser);

            List<Integer> userIds = new ArrayList<>();
            userIds.add(Integer.valueOf(bill2.getSalesmanId()));
            userIds.add(Integer.valueOf(bill2.getOperaterId()));
            List<User> userList = userService.getGzhIdsByUserId(new User(userIds));

            Customer customer = new Customer();
            customer.setId(bill2.getCustomerId());
            customer = customerBiz.getById(customer);
            if(customer.getGzhSend().equals("1")) {
                User u2 = new User();
                u2.setUsername(customer.getAdminUser());
                u2 = userService.getUserByUsername(u2);
                if(Utils.isNotEmpty(u2.getGzhId())){
                    userList.add(u2);
                }
            }

            for (User u: userList) {
                SettingGzh cm = new SettingGzh();
                TempSend tempSend2 = new TempSend();
                tempSend2.setTouser(u.getGzhId());
                tempSend2.setTemplateId("NmUIzARvtGDHaxf8PpXjMpU3H-Qy_5ZTj7St51Wd56s");
                tempSend2.setFirst("客户:"+bill2.getCustomer()+" 提单号:"+bill2.getBillOfLading()+" 车牌:"+bill2.getCarNo()+" 司机:"+bill2.getDriver() + " 电话:" + bill2.getDriverPhone());
                tempSend2.setKeyword1(DateUtil.getCurrentDate());
                tempSend2.setKeyword2(remark);
                tempSend2.setRemark("状态:已离厂");
                cm.setPrivateTemplate(tempSend2);
            }

            return JsonResult.ok("离厂成功");
        }
        return JsonResult.error("离厂失败");
    }

    //进港
    @Override
    @Transactional
    public JsonResult inPort_substitute(Bill bill, User loginUser) {
        if(Utils.isEmpty(bill.getPreEntryPic())
        ){
            return JsonResult.error("请填上传预录信息,操作失败!");
        }
        if (billBiz.inPort_substitute(bill, loginUser)) {
            return JsonResult.ok("进港信息补录成功");
        }
        return JsonResult.error("进港信息补录失败");
    }

    //进港
    @Override
    @Transactional
    public JsonResult inPort_gps(Bill bill, User loginUser) {
        String remark = bill.getRemark();
        Bill bill2 = billBiz.getById(bill);
        bill.setState("已进港");
        if (billBiz.inPort_gps(bill, loginUser)) {
            messageBiz.save(new Message("提单号:"+bill2.getBillOfLading()+"  司机已进港",
                    Integer.valueOf(bill2.getCustomerId()), "billState", bill2.getBillOfLading()), loginUser);
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("已进港");
            bp.setRemark(remark);
            billProcessBiz.save(bp, loginUser);
            int message = 0;
            try {
                Customer customer2 = customerBiz.getByName(new Customer(bill2.getCustomer())).get(0);
                message = Integer.valueOf(customer2.getMessage());
            } catch (Exception e) {
            }
            //624616【宁波甩手掌柜网络科技有限公司】您好,提单号:{1},司机师傅信息:{2},感谢您一直以来对甩手掌柜的支持,如有一问请联系客服(0574)28820591
//            PhoneUtils.sendMsg(bill2.getContactsPhone(), "624616",
//                    new String[]{bill2.getBillOfLading(),bill2.getCarNo()+"、"
//                            +bill2.getDriver()+"、"+bill2.getDriverPhone()+" 的运单已进港"}, message);
            List<Integer> userIds = new ArrayList<>();
            userIds.add(Integer.valueOf(bill2.getSalesmanId()));
            userIds.add(Integer.valueOf(bill2.getOperaterId()));
            List<User> userList = userService.getGzhIdsByUserId(new User(userIds));

//            Customer customer = new Customer();
//            customer.setId(bill2.getCustomerId());
//            customer = customerBiz.getById(customer);
//            if(customer.getGzhSend().equals("1")) {
//                User u2 = new User();
//                u2.setUsername(customer.getAdminUser());
//                u2 = userService.getUserByUsername(u2);
//                if(Utils.isNotEmpty(u2.getGzhId())){
//                    userList.add(u2);
//                }
//            }

            for (User u: userList) {
                if(Utils.isNotEmpty(u.getGzhId())) {
                    SettingGzh cm = new SettingGzh();
                    TempSend tempSend2 = new TempSend();
                    tempSend2.setTouser(u.getGzhId());
                    tempSend2.setTemplateId("NmUIzARvtGDHaxf8PpXjMpU3H-Qy_5ZTj7St51Wd56s");
                    tempSend2.setFirst("客户:"+bill2.getCustomer()+" 提单号:"+bill2.getBillOfLading()+" 车牌:"+bill2.getCarNo()+" 司机:"+bill2.getDriver() + " 电话:" + bill2.getDriverPhone());
                    tempSend2.setKeyword1(DateUtil.getCurrentDate());
                    tempSend2.setKeyword2(remark);
                    tempSend2.setRemark("状态:已进港");
                    cm.setPrivateTemplate(tempSend2);
                }
            }
            return JsonResult.ok("进港成功");
        }
        return JsonResult.error("进港失败");
    }

    //进港
    @Override
    @Transactional
    public JsonResult inPort(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
        if(Utils.isEmpty(bill.getArrivedHarbourDate())||Utils.isEmpty(bill.getArrivedHarbourLatitude())||
            Utils.isEmpty(bill.getArrivedHarbourLongitude())||Utils.isEmpty(bill.getPreEntryPic())
            ){
            return JsonResult.error("请填写进港信息,操作失败!");
        }
        bill.setState("已进港");
        if (billBiz.inPort(bill, loginUser)) {
            messageBiz.save(new Message("提单号:"+bill2.getBillOfLading()+"  司机已进港",
                    Integer.valueOf(bill2.getCustomerId()), "billState", bill2.getBillOfLading()), loginUser);
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("已进港");
            billProcessBiz.save(bp, loginUser);
            int message = 0;
            try {
                Customer customer2 = customerBiz.getByName(new Customer(bill2.getCustomer())).get(0);
                message = Integer.valueOf(customer2.getMessage());
            } catch (Exception e) {
            }
            //624616【宁波甩手掌柜网络科技有限公司】您好,提单号:{1},司机师傅信息:{2},感谢您一直以来对甩手掌柜的支持,如有一问请联系客服(0574)28820591
//            PhoneUtils.sendMsg(bill2.getContactsPhone(), "624616",
//                    new String[]{bill2.getBillOfLading(),bill2.getCarNo()+"、"
//                            +bill2.getDriver()+"、"+bill2.getDriverPhone()+" 的运单已进港"}, message);
            return JsonResult.ok("进港成功");
        }
        return JsonResult.error("进港失败");
    }

    //发起费用清单
    @Override
    @Transactional
    public JsonResult costBill(Bill bill, User loginUser) throws Exception  {
        Bill bill2 = billBiz.getById(bill);
        if("已做单已提箱已到厂已离厂已进港".indexOf(bill2.getState())==-1) {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        bill.setState("发起费用清单");
        String invoiceIdCustomer = "",InvoicenameCustomer = "",InvoicestateCustomer = "";
        String invoiceIdDriver = "",InvoicenameDriver = "",InvoicenameDriverName = "",InvoicestateDriver = "";
        String fobject = "司机";
        InvoiceName in1 = new InvoiceName();
        in1.setName(bill2.getCustomer());
        in1.setType("货代");
        try {
            List<InvoiceName> invoiceNameCustomer = invoiceNameBiz.getByName(in1);
            if(invoiceNameCustomer != null && invoiceNameCustomer.size()>0) {
                invoiceIdCustomer = invoiceNameCustomer.get(0).getId();
                InvoicenameCustomer = invoiceNameCustomer.get(0).getName();
                InvoicestateCustomer = invoiceNameCustomer.get(0).getState();
            }
        } catch (Exception e) {
        }
        try {
            InvoiceName in2 = new InvoiceName();
            in2.setType("司机");
            if(loginUser.getUsername().equals("13911223344")||bill2.getDriver().equals("")){
                in2.setName("现金车");
                List<InvoiceName> invoiceNameDriver = invoiceNameBiz.getByName2(in2);
                if(invoiceNameDriver != null && invoiceNameDriver.size()>0) {
                    invoiceIdDriver = invoiceNameDriver.get(0).getId();
                    InvoicenameDriver = invoiceNameDriver.get(0).getName();
                    InvoicenameDriverName = invoiceNameDriver.get(0).getDriverName();
                    InvoicestateDriver = invoiceNameDriver.get(0).getState();
                }
            } else if(loginUser.getUsername().equals("13957825295")){
                in2.setName("郑诚");
                List<InvoiceName> invoiceNameDriver = invoiceNameBiz.getByName2(in2);
                if(invoiceNameDriver != null && invoiceNameDriver.size()>0) {
                    invoiceIdDriver = invoiceNameDriver.get(0).getId();
                    InvoicenameDriver = invoiceNameDriver.get(0).getName();
                    InvoicenameDriverName = invoiceNameDriver.get(0).getDriverName();
                    InvoicestateDriver = invoiceNameDriver.get(0).getState();
                }
            /*
                甩手掌柜车队下面的司机抬头为自己司机的名字
                自己的司机就是在甩手掌柜车队下面
             */
            } else if(bill2.getCarTeamId().equals("214")) {
                in2.setName(bill2.getCarNo());
                List<InvoiceName> invoiceNameDriver = invoiceNameBiz.getByName(in2);
                if(invoiceNameDriver != null && invoiceNameDriver.size()>0) {
                    invoiceIdDriver = invoiceNameDriver.get(0).getId();
                    InvoicenameDriver = invoiceNameDriver.get(0).getName();
                    InvoicenameDriverName = invoiceNameDriver.get(0).getDriverName();
                    InvoicestateDriver = invoiceNameDriver.get(0).getState();
                }
            //除了甩手掌柜车队发起费用都要把抬头写成自己的车队
            } else {
                fobject = "车队";
                in2.setName(bill2.getCarTeam());
                in2.setType("车队");
                List<InvoiceName> invoiceNameDriver = invoiceNameBiz.getByName(in2);
                if(invoiceNameDriver != null && invoiceNameDriver.size()>0) {
                    invoiceIdDriver = invoiceNameDriver.get(0).getId();
                    InvoicenameDriver = invoiceNameDriver.get(0).getName();
                    InvoicestateDriver = invoiceNameDriver.get(0).getState();
                }
            }
        } catch (Exception e) {
        }
        if (billBiz.costBill(bill, loginUser)) {
            BillProcess bp2 = new BillProcess();
            bp2.setBillId(bill2.getId());
            List<BillProcess> bpList = billProcessBiz.list(bp2);
//            bpList = bpList.stream().filter(item -> item.getState().equals("已提箱")||item.getState().equals("已进港")).collect(Collectors.toList());
            CarPosition carPosition = new CarPosition();
            if(bpList!=null && bpList.size()>2) {
                for (BillProcess bp3 : bpList) {
                    if (bp3.getState().equals("已提箱")) {
                        carPosition.setDate1(bp3.getStateTime());
                    }
                    if (bp3.getState().equals("已进港")) {
                        carPosition.setDate2(bp3.getStateTime());
                    }
                }
            }

            carPosition.setBillId(bill.getId());
            String m = carPositionService.getDistanceByBillId(carPosition);
            DriverMileage driverMileage = new DriverMileage();
            driverMileage.setBillId(bill.getId());
            driverMileage.setDriverId(Integer.valueOf(bill2.getDriverId()));
            driverMileage.setDriver(bill2.getDriver());
            driverMileage.setCarNo(bill2.getCarNo());
            driverMileage.setAverageOilConsumption("0");
            driverMileage.setRoadToll("0");
            driverMileage.setMileage(m);
            Settings settings = new Settings();
            settings.setName("oil_price");
            driverMileage.setMiPrice(settingsBiz.getByName(settings).getValue());
            driverMileageService.save(driverMileage, loginUser);

            for (BillFee billFee: bill.getBillFeeList()) {
                if(Utils.isEmpty(billFee.getBillId())){
                    throw new Exception("请填写订单id,操作失败!");
                }
                billFee.setFobject(fobject);
                billFee.setCurrency("CNY");
                billFee.setExchangeRate("1");
                billFee.setInvoiceid(invoiceIdDriver);
                billFee.setInvoicename(InvoicenameDriver);
                billFee.setDriverName(InvoicenameDriverName);
                billFee.setInvoicestate(InvoicestateDriver);
                billFeeBiz.save(billFee, loginUser);

                //司机发起费用申请的同时,平台插入相对应的应收附加费
                /*MaintainFeeAttach maintainFeeAttach = new MaintainFeeAttach();
                if(Utils.isNotEmpty(billFee.getBoxType())){
                    maintainFeeAttach.setBoxType(billFee.getBoxType());
                }
                maintainFeeAttach.setName(billFee.getFeeName());
                maintainFeeAttach.setStandard(billFee.getStandard());
                maintainFeeAttach.setType("平台");
                List<MaintainFeeAttach> maintainFeeAttachList = maintainFeeAttachBiz.list(maintainFeeAttach,loginUser);
                if(maintainFeeAttachList!=null && maintainFeeAttachList.size()==1) {
                    maintainFeeAttach = maintainFeeAttachList.get(0);
                    if(maintainFeeAttach.getPrice()!=null){
                        billFee.setDriverName("");
                        BillFee billFee2 = (BillFee)billFee.clone();
                        billFee2.setType("应收");
                        billFee2.setPrice(maintainFeeAttach.getPrice());
                        billFee2.setFobject("货代");
                        billFee2.setFeeModules(maintainFeeAttach.getAtModules());
                        billFee2.setCurrency("CNY");
                        billFee2.setExchangeRate("1");
                        billFee2.setInvoiceid(invoiceIdCustomer);
                        billFee2.setInvoicename(InvoicenameCustomer);
                        billFee2.setInvoicestate(InvoicestateCustomer);
                        User auser = new User();
                        auser.setUserId(Integer.valueOf(bill2.getOperaterId()));
                        auser.setOrganizationId(1);
                        billFeeBiz.save(billFee2, loginUser);
                    }
                }*/
            }

            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("发起费用清单");
            billProcessBiz.save(bp, loginUser);

            //添加司机的运费
            BillFee billFee = new BillFee();
            billFee.setBillId(bill2.getId());
            billFee.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            billFee.setType("应付");
            billFee.setFeeName("运费");
            billFee.setFeeModules("c运费");
            billFee.setFobject("司机");
            billFee.setPrice(bill2.getDriverFreight());
            billFee.setCurrency("CNY");
            billFee.setExchangeRate("1");
            billFee.setInvoiceid(invoiceIdDriver);
            billFee.setInvoicename(InvoicenameDriver);
            billFee.setDriverName(InvoicenameDriverName);
            billFee.setInvoicestate(InvoicestateDriver);
            billFeeBiz.save(billFee, loginUser);

            //不等于温州车队
//            if (!bill2.getCarTeamId().equals("397")) {
            //港口等于宁波港
            if (bill2.getDock().equals("宁波港")) {
                billFee.setDriverName("");
                billFee.setBillId(bill2.getId());
                billFee.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
                billFee.setType("应收");
                billFee.setFeeName("条形码");
                billFee.setFeeModules("i其他");
                billFee.setFobject("货代");
                billFee.setPrice(bill2.getBoxType().indexOf("20") != -1? "10": "15");
                billFee.setCurrency("CNY");
                billFee.setExchangeRate("1");
                billFee.setInvoiceid(invoiceIdCustomer);
                billFee.setInvoicename(InvoicenameCustomer);
                billFee.setInvoicestate(InvoicestateCustomer);
                billFeeBiz.save(billFee, loginUser);

                billFee.setBillId(bill2.getId());
                billFee.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
                billFee.setType("应收");
                billFee.setFeeName("吊机费");
                billFee.setFeeModules("i其他");
                billFee.setFobject("货代");
                billFee.setPrice(bill2.getBoxType().indexOf("20") != -1? "20": "30");
                billFee.setCurrency("CNY");
                billFee.setExchangeRate("1");
                billFee.setInvoiceid(invoiceIdCustomer);
                billFee.setInvoicename(InvoicenameCustomer);
                billFee.setInvoicestate(InvoicestateCustomer);
                billFeeBiz.save(billFee, loginUser);

                billFee.setBillId(bill2.getId());
                billFee.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
                billFee.setType("应收");
                billFee.setFeeName("运费");
                billFee.setFeeModules("c运费");
                billFee.setFobject("货代");
                billFee.setPrice("0");
                billFee.setCurrency("CNY");
                billFee.setExchangeRate("1");
                billFee.setInvoiceid(invoiceIdCustomer);
                billFee.setInvoicename(InvoicenameCustomer);
                billFee.setInvoicestate(InvoicestateCustomer);
                billFeeBiz.save(billFee, loginUser);

            }


            /*billFee = new BillFee();
            billFee.setBillId(bill2.getId());
            billFee.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            billFee.setType("应收");
            billFee.setFeeName("运费");
            billFee.setFobject("货代");
            billFee.setPrice(bill2.getPrice());
            billFee.setCurrency("CNY");
            billFee.setExchangeRate("1");
            billFee.setInvoiceid(invoiceIdCustomer);
            billFee.setInvoicename(InvoicenameCustomer);
            billFee.setInvoicestate(InvoicestateCustomer);
            billFeeBiz.save(billFee, loginUser);*/

            //设置总应收应付的金额
//            setBillTotalFee_S_F(bill2, loginUser);

            return JsonResult.ok("发起费用清单成功");
        }
        return JsonResult.error("发起费用清单失败");
    }

    //直接发起费用清单
    @Override
    @Transactional
    public JsonResult directCostBill(Bill bill, User loginUser) throws Exception  {
//        Bill bill2 = billBiz.getById(bill);
//        if("已下单平台已接单已指派车队已做单已提箱已到厂已离厂已进港".indexOf(bill2.getState())==-1) {
//            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
//        }
        bill.setState("发起费用清单");
        if (billBiz.costBill(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("发起费用清单");
            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("发起费用清单成功");
        }
        return JsonResult.error("发起费用清单失败");
    }

    //应付费用审核
    @Override
    @Transactional
    public JsonResult payableVerify(Bill bill, User loginUser) {
        if(loginUser.getUserId()!=51 && !loginUser.getRoleIds().contains(UserConstant.DISPATCH_ROLE_ID)){
            return JsonResult.error("应付费用审核由调度和邱振达,操作失败!");
        }

        Bill bill2 = billBiz.getById(bill);
        if(!bill2.getState().equals("发起费用清单")) {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        bill.setState("应付费用已审核");
        if (billBiz.updateState(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("应付费用已审核");
            billProcessBiz.save(bp, loginUser);
//            if(feeRet) {
//                bp.setState("结单");
//                billProcessBiz.save(bp, loginUser);
//            }
            return JsonResult.ok("应付费用审核成功");
        }
        return JsonResult.error("应付费用审核失败");
    }

    //应收费用审核
    @Override
    @Transactional
    public JsonResult receivableVerify(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
//        if(!bill2.getSalesmanId().equals(loginUser.getUserId()+"")
//                && !bill2.getOperaterId().equals(loginUser.getUserId()+"")){
//            return JsonResult.error("应收费用审核由运单对应的客服和销售确认,操作失败!");
//        }
        if(!bill2.getState().equals("应付费用已审核")) {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        bill.setState("应收费用已审核");
//        bill.setState("结单");
        if (billBiz.updateState(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("应收费用已审核");
            billProcessBiz.save(bp, loginUser);
//            bp.setState("结单");
//            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("应收费用审核成功");
        }
        return JsonResult.error("应收费用审核失败");
    }

    //财务应收应付费用审核
    @Override
    @Transactional
    public JsonResult receivablePayableVerify(Bill bill, User loginUser) {
        if( loginUser.getUserId()!=52){
            return JsonResult.error("财务应收应付费用审核由黄月琴确认,操作失败!");
        }
        Bill bill2 = null;
        for(String ID : bill.getId().split(",")){
            bill2 = new Bill();
            bill2.setId(ID);
            bill2 = billBiz.getById(bill2);
            if(!bill2.getState().equals("应收费用已审核")) {
                return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
            }
    //        bill.setState("财务应收应付费用已审核");
            bill2.setState("结单");
            if (billBiz.updateState(bill2, loginUser)) {
                BillProcess bp = new BillProcess();
                bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
                bp.setBillId(bill2.getId());
                bp.setState("财务应收应付费用已审核");
                billProcessBiz.save(bp, loginUser);
                bp.setState("结单");
                billProcessBiz.save(bp, loginUser);
            }
        }
        return JsonResult.ok("应收费用审核成功");
    }

    public JsonResult listUnExamine() {
        List<Bill> billList = billBiz.listUnExamine();
        return JsonResult.ok().setData(billList);
    }

    //状态为已做单没有上传箱号,封号的单子,每天早上7点发给郑哥
//    @Scheduled(cron="0 0 7 * * ?")
    public void noBoxnumSealno() {
        Bill bill2 = new Bill();
        bill2.setKeywords("提醒没传箱封号");
        bill2.setState("已做单");

        User user = new User();
        user.setUserId(UserConstant.ADMIN_ID);
        user.setOrganizationId(UserConstant.ORGANIZATION_ID);

        StringBuilder sb = new StringBuilder("");
        List<Bill> billList = billBiz.list(bill2, user);
        if(billList != null && billList.size() > 0) {
            for(int i = 0; i < billList.size(); i ++) {
                boolean driverFee = false;
                Bill bill = billList.get(i);
                sb.append(bill.getBillOfLading()+",");
            }
        }
        int c = 0;
        if (Utils.isNotEmpty(billList)) {
            c = billList.size();
        }
        SettingGzh cm = new SettingGzh();
        TempSend tempSend2 = new TempSend();
        tempSend2.setTouser("owHBe6O8c3cPP-N5cmMYKQq8uQcM");
        tempSend2.setTemplateId("NmUIzARvtGDHaxf8PpXjMpU3H-Qy_5ZTj7St51Wd56s");
        tempSend2.setUrl("gzhsy.sszgjzx.com");
        tempSend2.setFirst("今日运单有"+c+"条记录未审核,");
        tempSend2.setKeyword1(DateUtil.getCurrentDate());
        tempSend2.setKeyword2("详情请点击");
        tempSend2.setRemark("");
        cm.setPrivateTemplate(tempSend2);
    }

    //蔡总现在不要9点给他发信息了
//    @Scheduled(cron="0 0 9 * * ?")
    public void billListAutoFinish() {
        List<Bill> billList = billBiz.listUnExamine();
        User user = new User();
        user.setUserId(UserConstant.ADMIN_ID);
        user.setOrganizationId(UserConstant.ORGANIZATION_ID);
        /*if(billList != null && billList.size() > 0) {
            for(int i = 0; i < billList.size(); i ++) {
                boolean driverFee = false;
                Bill bill = billList.get(i);
                SettingGzh cm = new SettingGzh();
                TempSend tempSend2 = new TempSend();
                tempSend2.setTouser("owHBe6O8c3cPP-N5cmMYKQq8uQcM");
                tempSend2.setTemplateId("NmUIzARvtGDHaxf8PpXjMpU3H-Qy_5ZTj7St51Wd56s");
                tempSend2.setUrl("gzhsy.sszgjzx.com");
                tempSend2.setFirst("提单号:"+bill.getBillOfLading()+" 车牌:"+bill.getCarNo()+" 司机:"+bill.getDriver());
                tempSend2.setKeyword1(DateUtil.getCurrentDate());
                tempSend2.setKeyword2("费用未审核,当前状态为:"+bill.getState());
                tempSend2.setRemark("");
                cm.setPrivateTemplate(tempSend2);
            }
        }*/
        int c = 0;
        if (Utils.isNotEmpty(billList)) {
            c = billList.size();
        }
        SettingGzh cm = new SettingGzh();
        TempSend tempSend2 = new TempSend();
        tempSend2.setTouser("owHBe6O8c3cPP-N5cmMYKQq8uQcM");
        tempSend2.setTemplateId("NmUIzARvtGDHaxf8PpXjMpU3H-Qy_5ZTj7St51Wd56s");
        tempSend2.setUrl("gzhsy.sszgjzx.com");
        tempSend2.setFirst("今日运单有"+c+"条记录未审核,");
        tempSend2.setKeyword1(DateUtil.getCurrentDate());
        tempSend2.setKeyword2("详情请点击");
        tempSend2.setRemark("");
        cm.setPrivateTemplate(tempSend2);
    }

    @Override
    public void feerun() {
        Bill bill = new Bill();
        List<Bill> bills = billBiz.list(bill,new User(1));
        for(Bill bill2: bills) {
            BillFee billFee = new BillFee();
            billFee.setBillId(bill2.getId());
//            setBillTotalFee_S_F(bill2, new User(1));
        }
    }

    @Override
    public JsonResult exportsFinance(JsonResult jr, Bill bill2) throws Exception {
        List<Bill> list = (List<Bill>)jr.getData();

        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        List<String> headRow = CollUtil.newArrayList("序号","客户简称","提单号","箱号","封号","箱型","拆装箱时间","拆装箱地址",
                "车队","司机姓名","司机联系信息","车牌号","进港码头","口岸",
                "操作","业务员","应收明细","应付明细","应收","应付","应收抬头","应付抬头","财务报销","易港通","应收不含税",
                "应付不含税","操作费","利润","收款日期");
        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 20); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        setRow(xssfRow, headRow, cellStyle,"");//为了使边框变成黑色
        // 合并单元格
        CellRangeAddress cra =new CellRangeAddress(0, 0, 0, headRow.size()-1); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(1, 1, 0, headRow.size()-1); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);

        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容
        xssfRow = xssfSheet.createRow(1);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(bill2.getDate2())) {
            date2 = " 到 "+bill2.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期："+bill2.getDate1().substring(0,10)+date2); //设置单元格内容
        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据

        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        fontStyle.setFontHeightInPoints((short) 14); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        setRow(xssfRow, headRow, cellStyle, "");
        //把List里面的数据写到excel中
        for (int i = 0; i < list.size(); i++) {
            Bill bill = list.get(i);
            List<BillFee> billFees = bill.getBillFeeList();
            StringBuilder yf = new StringBuilder();//应付
            StringBuilder ys = new StringBuilder();//应收
            StringBuilder yfInvoice = new StringBuilder();//应付抬头
            StringBuilder ysInvoice = new StringBuilder();//应收抬头
            int ygt = 0;//易港通
            int cwbx = 0;//财务报销

            Map<String,Double> mapyf = new HashMap<>();//应付抬头
            Map<String,Double> mapys = new HashMap<>();//应收抬头
            for (int j = 0; j < billFees.size(); j ++) {
                BillFee bf = billFees.get(j);
                if(bf.getType().equals("应付")) {
                    yf.append(bf.getFeeName()+":"+bf.getPrice()+",");
                    Double fee = mapyf.get(bf.getInvoicename());
                    if(bf.getInvoicename().equals("易港通")){
                        ygt += Integer.valueOf(bf.getPrice());
                    } else if(bf.getInvoicename().equals("财务报销")){
                        cwbx += Integer.valueOf(bf.getPrice());
                    } else if(fee == null) {
                        mapyf.put(bf.getInvoicename(),Double.valueOf(bf.getPrice()));
                    } else {
                        mapyf.put(bf.getInvoicename(),Double.valueOf(bf.getPrice())+fee);
                    }
                } else if(bf.getType().equals("应收")) {
                    ys.append(bf.getFeeName()+":"+bf.getPrice()+",");
                    Double fee = mapys.get(bf.getInvoicename());
                    if(fee == null) {
                        mapys.put(bf.getInvoicename(),Double.valueOf(bf.getPrice()));
                    } else {
                        mapys.put(bf.getInvoicename(),Double.valueOf(bf.getPrice())+fee);
                    }
                }
            }
            for (String key : mapyf.keySet()) {
                yfInvoice.append(key+":"+mapyf.get(key));
            }
            for (String key : mapys.keySet()) {
                ysInvoice.append(key+":"+mapys.get(key));
            }
            xssfRow = xssfSheet.createRow(i+3);
            //创建每个单元格Cell，即列的数据
            xssfCell = xssfRow.createCell(0); //创建单元格
            xssfCell.setCellValue(i+1); //设置单元格内容
            xssfCell = xssfRow.createCell(1); //创建单元格
            xssfCell.setCellValue(bill.getCustomer()); //设置单元格内容
            List<String> row = CollUtil.newArrayList((i+1)+"", bill.getCustomer(), bill.getBillOfLading(), bill.getBoxNum(), bill.getSealno(),
                    bill.getBoxType(), bill.getPackDate(), bill.getFactoryAddress(),  bill.getCarTeam(), bill.getDriver(),
                    bill.getDriverPhone(), bill.getCarNo(),
                    bill.getArrivalPort(), bill.getDock(), bill.getOperater(), bill.getSalesman(),ys.toString(), yf.toString(),
                    bill.getFeeTotalS(),bill.getFeeTotalF(),ysInvoice.toString(), yfInvoice.toString(), cwbx +"", ygt+"",
                    bill.getFeeTotalSDiscount()+"", bill.getFeeTotalFDiscount()+"", " ", " ", " ");
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 12); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
//            setRow(xssfRow,row, cellStyle, ",13,14,15,22,23,26,27,28,29,");
            setRow(xssfRow,row, cellStyle, "");
        }
        OutputStream os = new FileOutputStream(new File(Constants.UPLOAD_DIR + "file/finance.xlsx"));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();


        return JsonResult.error("excel 生成成功!");
    }

    @Override
    public JsonResult exportsFinance2(List<Bill> list, List<String> feeNamesys, List<String> feeNamesyf, Bill bill2) throws Exception {
        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(bill2.getDate2())) {
            date2 = " 到 "+bill2.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期："+bill2.getDate1().substring(0,10)+date2); //设置单元格内容

        List<String> headRow = CollUtil.newArrayList("序号","日期","客户简称","提单号","箱型","箱号", "封号","工厂地址",
                "提箱场站","还箱场站","车队","司机","司机电话","车牌号","口岸","操作员","业务员","应收明细");
        headRow.addAll(feeNamesys);
        headRow.remove(headRow.size()-1);
        headRow.add("应收合计");
        headRow.add("应付明细");
        headRow.addAll(feeNamesyf);
        headRow.add("财务报销");
        headRow.add("应付合计");
        headRow.add("应收不含税");
        headRow.add("应付不含税");
        headRow.add("操作费");
        headRow.add("利润");
        headRow.add("收款日期");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色

        headRow = CollUtil.newArrayList("序号","日期","客户简称","提单号","箱型","箱号", "封号","工厂地址",
                "提箱场站","还箱场站","车队","司机","司机电话","车牌号","口岸","操作员","业务员");
        headRow.addAll(feeNamesys);
        headRow.add("应收合计");
        headRow.addAll(feeNamesyf);
        headRow.add("财务报销");
        headRow.add("易港通");
        headRow.add("应付合计");
        headRow.add("应收不含税");
        headRow.add("应付不含税");
        headRow.add("操作费");
        headRow.add("利润");
        headRow.add("收款日期");
        xssfRow = xssfSheet.createRow(4);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
//        xssfSheet.createFreezePane( 0, 5, 0, 0);//固定列

        String[] letters = new String[]{"K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
                "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR",
                "AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ",
                "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB",
                "CC","CD","CE","CF","CG","CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT",
                "CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL",
                "DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ"};

        //把List里面的数据写到excel中
        for (int i = 0; i < list.size(); i++) {
            Bill bill = list.get(i);
            List<BillFee> billFees = bill.getBillFeeList();

            xssfRow = xssfSheet.createRow(i+5);
            //创建每个单元格Cell，即列的数据
            xssfCell = xssfRow.createCell(0); //创建单元格
            xssfCell.setCellValue(i+1); //设置单元格内容
            xssfCell = xssfRow.createCell(1); //创建单元格
            xssfCell.setCellValue(bill.getCustomer()); //设置单元格内容

            String add = "";
            List<AddressFactory> addressFactories = bill.getAddressFactoryList();
            if(addressFactories!=null && addressFactories.size() > 0) {
                for(int c = 0; c < addressFactories.size(); c ++) {
                    AddressFactory addressF = addressFactories.get(c);
                    add += addressF.getFaddress() + ",";
                }
            }

            List<String> row = CollUtil.newArrayList((i+1)+"", DateUtil.formatDate(DateUtil.parseDate(bill.getPackDate()),"MM-dd"),
                    bill.getCustomer(), bill.getBillOfLading(), bill.getBoxType(),
                    bill.getBoxNum(), bill.getSealno(), add,
                    bill.getUpYard()==null?"/":bill.getUpYard(), bill.getArrivalPort()==null?"/":bill.getArrivalPort(),
                    bill.getCarTeam(), bill.getDriver(), bill.getDriverPhone(), bill.getCarNo(),
                    bill.getDock(),bill.getOperater(),bill.getSalesman()
            );
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

            float temp_ret = 0;
            for(int t = 0; t < feeNamesys.size(); t ++) {
                String feeName = feeNamesys.get(t);
                temp_ret = 0;
                for (int j = 0; j < billFees.size(); j ++) {
                    BillFee bf = billFees.get(j);
                    if(bf.getType().equals("应收") && bf.getFeeName().equals(feeName)) {
                        if(Utils.isNotEmpty(bf.getPrice())) {
                            temp_ret += Float.parseFloat(bf.getPrice());
                        }
                    }
                }
                row.add(temp_ret + "");
            }
            row.add("function::SUM("+letters[7]+(i+6)+":"+letters[feeNamesys.size()+6]+(i+6)+")");

            float cwbx = 0,ygt = 0;
            for(int t = 0; t < feeNamesyf.size(); t ++) {
                cwbx = 0;
                ygt = 0;
                temp_ret = 0;
                String feeName = feeNamesyf.get(t);
                for (int j = 0; j < billFees.size(); j ++) {
                    BillFee bf = billFees.get(j);
                    if(Utils.isNotEmpty(bf.getPrice())) {
                        if (bf.getType().equals("应付")) {
                            if (bf.getFeeName().equals(feeName) && !bf.getInvoicename().equals("易港通")
                                    && !bf.getInvoicename().equals("财务报销")) {
                                temp_ret += Float.parseFloat(bf.getPrice());
                            } else if (bf.getInvoicename().equals("易港通")) {
                                ygt += Float.parseFloat(bf.getPrice());
                            } else if (bf.getInvoicename().equals("财务报销")) {
                                cwbx += Float.parseFloat(bf.getPrice());
                            }
                        }
                    }
                }
                row.add(temp_ret + "");
            }
            row.add(cwbx+"");
            row.add(ygt+"");
            row.add("function::SUM("+letters[feeNamesys.size()+8]+(i+6)+":"+letters[feeNamesys.size()+feeNamesyf.size()+9]+(i+6)+")");
            float ysTotalNoTax = 0, yfTotalNoTax = 0;
            for (BillFee bf2 : billFees) {
                if(Utils.isNotEmpty(bf2.getPrice())) {
                    if (bf2.getType().equals("应收")) {
                        float cc = Float.parseFloat(bf2.getPrice()) * Float.parseFloat(bf2.getExchangeRate());
                        if (bf2.getInvoicestate().equals("1")) {
                            cc = SszgUtils.getInvoiceNum(cc, bf2.getDriverName());
                        }
                        ysTotalNoTax += cc;
                    } else {
                        float cc = Float.parseFloat(bf2.getPrice()) * Float.parseFloat(bf2.getExchangeRate());
                        if (bf2.getInvoicestate().equals("1")) {
                            cc = SszgUtils.getInvoiceNum(cc, bf2.getDriverName());
                        }
                        yfTotalNoTax += cc;
                    }
                }
            }
            row.add(Utils.twoDot(ysTotalNoTax));
            row.add(Utils.twoDot(yfTotalNoTax));
            row.add("10");
            row.add(Utils.twoDot(ysTotalNoTax-yfTotalNoTax-10)+"");
            row.add("");

            String numColumns = ",";
            for(int t = 0; t < feeNamesys.size(); t ++) {
                numColumns += (t+17)+",";
            }
            //加2个列明的易港通和财务报销
            for(int t = 0; t < feeNamesyf.size()+2; t ++) {
                numColumns += (t+18+feeNamesys.size())+",";
            }
            numColumns += 21+feeNamesys.size()+feeNamesyf.size()+",";
            numColumns += 22+feeNamesys.size()+feeNamesyf.size()+",";
            numColumns += 23+feeNamesys.size()+feeNamesyf.size()+",";
            numColumns += 24+feeNamesys.size()+feeNamesyf.size()+",";

            setRow(xssfRow,row, cellStyle, numColumns);
        }

        // 合并单元格
        CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 25+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(1, 1, 0, 25+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(2, 2, 0, 25+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        if (16 + feeNamesys.size() != 17) {
            cra =new CellRangeAddress(3, 3, 17, 16+feeNamesys.size()); // 起始行, 终止行, 起始列, 终止列
            xssfSheet.addMergedRegion(cra);
        }
        cra =new CellRangeAddress(3, 3, 18+feeNamesys.size(), 19+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        for(int i = 0; i <=25+feeNamesys.size()+feeNamesyf.size(); i ++) {
            if(i < 17 || i == (17+feeNamesys.size()) || i > (19+feeNamesys.size()+feeNamesyf.size())){
                cra =new CellRangeAddress(3, 4, i, i); // 起始行, 终止行, 起始列, 终止列
                xssfSheet.addMergedRegion(cra);
                RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
            }
        }

        xssfRow = xssfSheet.createRow(5+list.size());
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("合计:"); //设置单元格内容


        for(int i =0; i <= feeNamesys.size() + feeNamesyf.size() + 7; i ++) {
            xssfCell = xssfRow.createCell(17+i); //创建单元格
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellType(CellType.NUMERIC);
            xssfCell.setCellFormula("SUM("+letters[i+7]+"6:"+letters[i+7]+(list.size()+5)+")"); //设置单元格内容
        }
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(18+feeNamesys.size() + feeNamesyf.size() + 7); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue(""); //设置单元格内容

        cra =new CellRangeAddress(5+list.size(), 5+list.size(), 0, 16); // 起始行, 终止行, 起始列, 终止列
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File(Constants.UPLOAD_DIR + "file/finance.xlsx"));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();

        return JsonResult.error("excel 生成成功!");
    }

    @Override
    public JsonResult exportsFinanceClassify(List<Bill> list, Bill bill2) throws Exception {
        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(bill2.getDate2())) {
            date2 = " 到 "+bill2.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期："+bill2.getDate1().substring(0,10)+date2); //设置单元格内容

        List<String> feeNamesys = new ArrayList<>();
        feeNamesys.add("运费");
        feeNamesys.add("提进");
        feeNamesys.add("其他");
        feeNamesys.add("单项");
        feeNamesys.add("采购");
        feeNamesys.add("报关费");
        List<String> feeNamesyf = new ArrayList<>();
        feeNamesyf.add("运费");
        feeNamesyf.add("提进");
        feeNamesyf.add("其他");
        feeNamesyf.add("单项");
        feeNamesyf.add("采购");
        feeNamesyf.add("报关费");
        feeNamesyf.add("易港通");
        feeNamesyf.add("财务报销");

        List<String> headRow = CollUtil.newArrayList("序号","日期","客户简称","提单号","箱型","箱号", "封号","工厂地址",
                "提箱场站","还箱场站","车队","司机","司机电话","车牌号","口岸","加封地","操作员","业务员","应收明细");
        headRow.addAll(feeNamesys);
        headRow.remove(headRow.size()-1);
        headRow.add("应收合计");
        headRow.add("应付明细");
        headRow.addAll(feeNamesyf);
        headRow.remove(headRow.size()-1);
        headRow.add("应付合计");
        headRow.add("应收不含税");
        headRow.add("应付不含税");
        headRow.add("折扣金额");
        headRow.add("操作费");
        headRow.add("利润");
        headRow.add("收款日期");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色

        headRow = CollUtil.newArrayList("序号","日期","客户简称","提单号","箱型","箱号", "封号","工厂地址",
                "提箱场站","还箱场站","车队","司机","司机电话","车牌号","口岸","加封地","操作员","业务员");
        headRow.addAll(feeNamesys);
        headRow.add("应收合计");
        headRow.addAll(feeNamesyf);
        headRow.add("应付合计");
        headRow.add("应收不含税");
        headRow.add("应付不含税");
        headRow.add("折扣金额");
        headRow.add("操作费");
        headRow.add("利润");
        headRow.add("收款日期");
        xssfRow = xssfSheet.createRow(4);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
//        xssfSheet.createFreezePane( 0, 5, 0, 0);//固定列

        String[] letters = new String[]{"K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
                "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR",
                "AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ",
                "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB",
                "CC","CD","CE","CF","CG","CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT",
                "CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL",
                "DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ"};

        //把List里面的数据写到excel中
        for (int i = 0; i < list.size(); i++) {
            Bill bill = list.get(i);
            List<BillFee> billFees = bill.getBillFeeList();

            xssfRow = xssfSheet.createRow(i+5);
            //创建每个单元格Cell，即列的数据
            xssfCell = xssfRow.createCell(0); //创建单元格
            xssfCell.setCellValue(i+1); //设置单元格内容
            xssfCell = xssfRow.createCell(1); //创建单元格
            xssfCell.setCellValue(bill.getCustomer()); //设置单元格内容

            String add = "";
            List<AddressFactory> addressFactories = bill.getAddressFactoryList();
            if(addressFactories!=null && addressFactories.size() > 0) {
                for(int c = 0; c < addressFactories.size(); c ++) {
                    AddressFactory addressF = addressFactories.get(c);
                    add += addressF.getFaddress() + ",";
                }
            }

            List<String> row = CollUtil.newArrayList((i+1)+"", DateUtil.formatDate(DateUtil.parseDate(bill.getPackDate()),"MM-dd"),
                    bill.getCustomer(), bill.getBillOfLading(), bill.getBoxType(),
                    bill.getBoxNum(), bill.getSealno(), add,
                    bill.getUpYard()==null?"/":bill.getUpYard(), bill.getArrivalPort()==null?"/":bill.getArrivalPort(),
                    bill.getCarTeam(), bill.getDriver(), bill.getDriverPhone(), bill.getCarNo(),
                    bill.getDock(),bill.getSealedLand(),bill.getOperater(),bill.getSalesman()
            );
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

//            float temp_ret = 0;
//            for(int t = 0; t < feeNamesys.size(); t ++) {
//                String feeName = feeNamesys.get(t);
//                temp_ret = 0;
//                for (int j = 0; j < billFees.size(); j ++) {
//                    BillFee bf = billFees.get(j);
//                    if(bf.getType().equals("应收") && bf.getFeeName().equals(feeName)) {
//                        if(Utils.isNotEmpty(bf.getPrice())) {
//                            temp_ret += Float.parseFloat(bf.getPrice());
//                        }
//                    }
//                }
//                row.add(temp_ret + "");
//            }
            float temp_ret = 0;
            for(int t = 0; t < feeNamesys.size(); t ++) {
                String feeName = feeNamesys.get(t);
                temp_ret = 0;
                for (int j = 0; j < billFees.size(); j ++) {
                    BillFee bf = billFees.get(j);
                    if(bf.getType().equals("应收") && bf.getFeeModules().equals(feeName)) {
                        if(Utils.isNotEmpty(bf.getPrice())) {
                            temp_ret += Float.parseFloat(bf.getPrice());
                        }
                    }
                }
                row.add(temp_ret + "");
            }
            row.add("function::SUM("+letters[8]+(i+6)+":"+letters[feeNamesys.size()+7]+(i+6)+")");

//            float cwbx = 0,ygt = 0;
//            for(int t = 0; t < feeNamesyf.size(); t ++) {
//                cwbx = 0;
//                ygt = 0;
//                temp_ret = 0;
//                String feeName = feeNamesyf.get(t);
//                for (int j = 0; j < billFees.size(); j ++) {
//                    BillFee bf = billFees.get(j);
//                    if(Utils.isNotEmpty(bf.getPrice())) {
//                        if (bf.getType().equals("应付")) {
//                            if (bf.getFeeName().equals(feeName) && !bf.getInvoicename().equals("易港通")
//                                    && !bf.getInvoicename().equals("财务报销")) {
//                                temp_ret += Float.parseFloat(bf.getPrice());
//                            } else if (bf.getInvoicename().equals("易港通")) {
//                                ygt += Float.parseFloat(bf.getPrice());
//                            } else if (bf.getInvoicename().equals("财务报销")) {
//                                cwbx += Float.parseFloat(bf.getPrice());
//                            }
//                        }
//                    }
//                }
//                row.add(temp_ret + "");
//            }
//            row.add(cwbx+"");
//            row.add(ygt+"");
            for(int t = 0; t < feeNamesyf.size(); t ++) {
                temp_ret = 0;
                String feeName = feeNamesyf.get(t);
                for (int j = 0; j < billFees.size(); j ++) {
                    BillFee bf = billFees.get(j);

                    if(bf.getType().equals("应付") && bf.getFeeModules().equals(feeName)) {
                        if(Utils.isNotEmpty(bf.getPrice())) {
                            temp_ret += Float.parseFloat(bf.getPrice());
                        }
                    }
                }
                row.add(temp_ret + "");
            }
            row.add("function::SUM("+letters[feeNamesys.size()+9]+(i+6)+":"+letters[feeNamesys.size()+feeNamesyf.size()+8]+(i+6)+")");
            float ysTotalNoTax = 0, yfTotalNoTax = 0;
            for (BillFee bf2 : billFees) {
                if(Utils.isNotEmpty(bf2.getPriceNoTax())) {
                    if (bf2.getType().equals("应收")) {
                        float cc = Float.parseFloat(bf2.getPriceNoTax());
                        ysTotalNoTax += cc;
                    } else {
                        float cc = Float.parseFloat(bf2.getPriceNoTax());
                        yfTotalNoTax += cc;
                    }
                }
            }
            row.add(Utils.twoDot(ysTotalNoTax));
            row.add(Utils.twoDot(yfTotalNoTax));
            int profitIndex = feeNamesys.size()+feeNamesyf.size()+6;
            row.add("function::TRUNC(IMSUB(IMSUB(IMSUB("+letters[profitIndex+3]+(i+6)+","+letters[profitIndex]+(i+6)+"),"+letters[profitIndex+1]+(i+6)+"),"+letters[profitIndex+2]+(i+6)+"),2)");
            row.add("10");
//            row.add(Utils.twoDot(ysTotalNoTax-yfTotalNoTax-10)+"");
            profitIndex = feeNamesys.size()+feeNamesyf.size()+10;
            row.add("function::TRUNC(IMSUB(IMSUB("+letters[profitIndex]+(i+6)+","+letters[profitIndex+1]+(i+6)+"),"+letters[profitIndex+3]+(i+6)+"),2)");
            row.add("");
            String numColumns = ",";
            for(int t = 0; t < feeNamesys.size() + feeNamesys.size() + 8; t ++) {
                numColumns += (t+18)+",";
            }
            setRow(xssfRow,row, cellStyle, numColumns);
        }

        // 合并单元格
        CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 23+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(1, 1, 0, 23+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(2, 2, 0, 23+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(3, 3, 18, 17+feeNamesys.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(3, 3, 19+feeNamesys.size(), 18+feeNamesys.size()+feeNamesyf.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        for(int i = 0; i <=25+feeNamesys.size()+feeNamesyf.size(); i ++) {
            if(i < 18 || i == (18+feeNamesys.size()) || i > (18+feeNamesys.size()+feeNamesyf.size())){
                cra =new CellRangeAddress(3, 4, i, i); // 起始行, 终止行, 起始列, 终止列
                xssfSheet.addMergedRegion(cra);
                RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
            }
        }

        xssfRow = xssfSheet.createRow(5+list.size());
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("合计:"); //设置单元格内容

        for(int i =0; i <= feeNamesys.size() + feeNamesyf.size() + 6; i ++) {
            xssfCell = xssfRow.createCell(18+i); //创建单元格
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellType(CellType.NUMERIC);
            xssfCell.setCellFormula("SUM("+letters[i+8]+"6:"+letters[i+8]+(list.size()+5)+")"); //设置单元格内容
        }
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(20+feeNamesys.size() + feeNamesyf.size() + 5); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue(""); //设置单元格内容

        cra =new CellRangeAddress(5+list.size(), 5+list.size(), 0, 17); // 起始行, 终止行, 起始列, 终止列
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File(Constants.UPLOAD_DIR + "file/finance.xlsx"));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();

        return JsonResult.ok("excel 生成成功!");
    }

    @Override
    public JsonResult exportsCarTeam(List<Bill> list,List<String> feelist, Bill bill2) throws Exception {

        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String to = bill2.getCustomer();
        if(StringUtil.isNotBlank(bill2.getDriver())) {
            to = bill2.getDriver();
        } else if(StringUtil.isNotBlank(bill2.getCarNo())) {
            to = bill2.getCarNo();
        } else if(StringUtil.isNotBlank(bill2.getCarTeam())) {
            to = bill2.getCarTeam();
        }
        xssfCell.setCellValue("To："+to); //设置单元格内容

        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(9+feelist.size()-4); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(bill2.getDate2())) {
            date2 = " 到 "+bill2.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期："+bill2.getDate1().substring(0,10)+date2); //设置单元格内容

        List<String> headRow = CollUtil.newArrayList("序号","日期","提单号","箱型","箱号","工厂地址",
               "提箱场站","还箱场站","车牌号");
        headRow.addAll(feelist);
        headRow.add("小计");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
        xssfSheet.createFreezePane( 0, 4, 0, 0);

        String[] letters = new String[]{"J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
                "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR",
                "AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ",
                "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT"};

        //把List里面的数据写到excel中
        for (int i = 0; i < list.size(); i++) {
            Bill bill = list.get(i);
            List<BillFee> billFees = bill.getBillFeeList();

            xssfRow = xssfSheet.createRow(i+4);
            //创建每个单元格Cell，即列的数据
            xssfCell = xssfRow.createCell(0); //创建单元格
            xssfCell.setCellValue(i+1); //设置单元格内容
            xssfCell = xssfRow.createCell(1); //创建单元格
            xssfCell.setCellValue(bill.getCustomer()); //设置单元格内容

            String add = "";
            List<AddressFactory> addressFactories = bill.getAddressFactoryList();
            if(addressFactories!=null && addressFactories.size() > 0) {
                for(int c = 0; c < addressFactories.size(); c ++) {
                    AddressFactory addressF = addressFactories.get(c);
                    add += addressF.getFaddress() + ",";
                }
            }

            List<String> row = CollUtil.newArrayList((i+1)+"", DateUtil.formatDate(DateUtil.parseDate(bill.getPackDate()),"MM-dd"),
                    bill.getBillOfLading(), bill.getBoxType(),
                    bill.getBoxNum(), add,
                    bill.getUpYard(), bill.getArrivalPort()=="null"?"":bill.getArrivalPort(), bill.getCarNo() );
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

            float temp_ret = 0;
            for(int t = 0; t < feelist.size(); t ++) {
                String feename = feelist.get(t);
                temp_ret = 0;
                for (int j = 0; j < billFees.size(); j ++) {
                    BillFee bf = billFees.get(j);
                    if(bf.getFeeName().equals(feename)) {
                        if(Utils.isNotEmpty(bf.getPrice())) {
                            temp_ret += Float.parseFloat(bf.getPrice());
                        }
                    }
                }
                row.add(temp_ret+"");
            }
            row.add("function::SUM("+letters[0]+(i+5)+":"+letters[feelist.size()-1]+(i+5)+")");

            setRow(xssfRow,row, cellStyle, ",9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,");
        }

        xssfRow = xssfSheet.createRow(4+list.size());
//        List<String> row = new ArrayList<>();
//        row.add("");row.add("");row.add("");row.add("");row.add("");
//        row.add("");row.add("");row.add("");row.add("");
//        setRowNoValue(xssfRow,row, cellStyle,"");
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("合计:"); //设置单元格内容


        for(int i =0; i <= feelist.size(); i ++) {
            xssfCell = xssfRow.createCell(9+i); //创建单元格
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellType(CellType.NUMERIC);
            xssfCell.setCellFormula("SUM("+letters[i]+"5:"+letters[i]+(list.size()+4)+")"); //设置单元格内容
        }

        // 合并单元格
        CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 9+feelist.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(1, 1, 0, 9+feelist.size()); // 起始行, 终止行, 起始列, 终止列

        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(2, 2, 0, 9+feelist.size()-5); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(2, 2, 9+feelist.size()-4, 9+feelist.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(4+list.size(), 4+list.size(), 0, 8); // 起始行, 终止行, 起始列, 终止列
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File( Constants.UPLOAD_DIR + "file/customer.xlsx"));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();

        return JsonResult.error("excel 生成成功!");
    }

    @Override
    public JsonResult exportsCustomer(List<Bill> list,List<String> feelist, Bill bill2) throws Exception {

        //https://www.cnblogs.com/janson071/p/10119935.html
        //https://www.cnblogs.com/pejsidney/p/8383843.html
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();
        xssfSheet.setForceFormulaRecalculation(true);//公式不自动执行的问题

        //创建行
        XSSFRow xssfRow;

        //创建列，即单元格Cell
        XSSFCell xssfCell;

        xssfRow = xssfSheet.createRow(0);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格*

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        Font fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 18); // 大小
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("宁波甩手掌柜供应链有限公司"); //设置单元格内容

        xssfRow = xssfSheet.createRow(1);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("Tel：0574-28820592"); //设置单元格内容

        xssfRow = xssfSheet.createRow(2);
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 12); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String to = bill2.getCustomer();
        if(StringUtil.isNotBlank(bill2.getDriver())) {
            to = bill2.getDriver();
        } else if(StringUtil.isNotBlank(bill2.getCarNo())) {
            to = bill2.getCarNo();
        } else if(StringUtil.isNotBlank(bill2.getCarTeam())) {
            to = bill2.getCarTeam();
        }
        xssfCell.setCellValue("To："+to); //设置单元格内容

        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(10+feelist.size()-4); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        xssfCell.setCellStyle(cellStyle);
        String date2 = "";
        if (Utils.isNotEmpty(bill2.getDate2())) {
            date2 = " 到 "+bill2.getDate2().substring(0, 10);
        }
        xssfCell.setCellValue("账期："+bill2.getDate1().substring(0,10)+date2); //设置单元格内容

        List<String> headRow = CollUtil.newArrayList("序号","日期","客户简称","提单号","箱型","箱号","工厂地址",
               "提箱场站","还箱场站","车牌号");
        headRow.addAll(feelist);
        headRow.add("小计");
        xssfRow = xssfSheet.createRow(3);
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        setRow(xssfRow, headRow, cellStyle, "");//为了使边框变成黑色
        xssfSheet.createFreezePane( 0, 4, 0, 0);

        String[] letters = new String[]{"K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z",
                "AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR",
                "AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ",
                "BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT"};

        //把List里面的数据写到excel中
        for (int i = 0; i < list.size(); i++) {
            Bill bill = list.get(i);
            List<BillFee> billFees = bill.getBillFeeList();

            xssfRow = xssfSheet.createRow(i+4);
            //创建每个单元格Cell，即列的数据
            xssfCell = xssfRow.createCell(0); //创建单元格
            xssfCell.setCellValue(i+1); //设置单元格内容
            xssfCell = xssfRow.createCell(1); //创建单元格
            xssfCell.setCellValue(bill.getCustomer()); //设置单元格内容
            String add = "";
            List<AddressFactory> addressFactories = bill.getAddressFactoryList();
            if(addressFactories!=null && addressFactories.size() > 0) {
                for(int c = 0; c < addressFactories.size(); c ++) {
                    AddressFactory addressF = addressFactories.get(c);
                    add += addressF.getFaddress() + ",";
                }
            }

            List<String> row = CollUtil.newArrayList((i+1)+"", DateUtil.formatDate(DateUtil.parseDate(bill.getPackDate()),"MM-dd"),
                    bill.getCustomer(), bill.getBillOfLading(), bill.getBoxType(),
                    bill.getBoxNum(), add,
                    bill.getUpYard(), bill.getArrivalPort()=="null"?"":bill.getArrivalPort(), bill.getCarNo() );
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setBold(false); // 加粗
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);

            float temp_ret = 0;
            for(int t = 0; t < feelist.size(); t ++) {
                String feename = feelist.get(t);
                temp_ret = 0;
                for (int j = 0; j < billFees.size(); j ++) {
                    BillFee bf = billFees.get(j);
                    if(bf.getFeeName().equals(feename)) {
                        if(Utils.isNotEmpty(bf.getPrice())) {
                            temp_ret += Float.parseFloat(bf.getPrice());
                        }
                    }
                }
                row.add(temp_ret + "");
            }
            row.add("function::SUM("+letters[0]+(i+5)+":"+letters[feelist.size()-1]+(i+5)+")");

            setRow(xssfRow,row, cellStyle, ",10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,");
        }

        xssfRow = xssfSheet.createRow(4+list.size());
//        List<String> row = new ArrayList<>();
//        row.add("");row.add("");row.add("");row.add("");row.add("");
//        row.add("");row.add("");row.add("");row.add("");
//        setRowNoValue(xssfRow,row, cellStyle,"");
        //创建每个单元格Cell，即列的数据
        xssfCell = xssfRow.createCell(0); //创建单元格
        fontStyle = xssfWorkbook.createFont(); // 字体样式
        fontStyle.setBold(true); // 加粗
        fontStyle.setFontName("宋体"); // 字体
        fontStyle.setFontHeightInPoints((short) 10); // 大小
        cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 将字体样式添加到单元格样式中
        cellStyle.setFont(fontStyle);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        xssfCell.setCellStyle(cellStyle);
        xssfCell.setCellValue("合计:"); //设置单元格内容


        for(int i =0; i <= feelist.size(); i ++) {
            xssfCell = xssfRow.createCell(10+i); //创建单元格
            fontStyle = xssfWorkbook.createFont(); // 字体样式
            fontStyle.setFontName("宋体"); // 字体
            fontStyle.setFontHeightInPoints((short) 10); // 大小
            cellStyle = xssfWorkbook.createCellStyle(); // 单元格样式
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 将字体样式添加到单元格样式中
            cellStyle.setFont(fontStyle);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            xssfCell.setCellStyle(cellStyle);
            xssfCell.setCellType(CellType.NUMERIC);
            xssfCell.setCellFormula("SUM("+letters[i]+"5:"+letters[i]+(list.size()+4)+")"); //设置单元格内容
        }

        // 合并单元格
        CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 10+feelist.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(1, 1, 0, 10+feelist.size()); // 起始行, 终止行, 起始列, 终止列

        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(2, 2, 0, 10+feelist.size()-5); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(2, 2, 10+feelist.size()-4, 10+feelist.size()); // 起始行, 终止行, 起始列, 终止列
        xssfSheet.addMergedRegion(cra);
        cra =new CellRangeAddress(4+list.size(), 4+list.size(), 0, 9); // 起始行, 终止行, 起始列, 终止列
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, xssfSheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, xssfSheet);
        xssfSheet.addMergedRegion(cra);

        OutputStream os = new FileOutputStream(new File( Constants.UPLOAD_DIR + "file/customer.xlsx"));
        //用输出流写到excel
        xssfWorkbook.write(os);
        os.flush();
        os.close();

        return JsonResult.error("excel 生成成功!");
    }

    public void setRow(XSSFRow xssfRow,List<String> row, XSSFCellStyle cellStyle, String changeNum) {
        //创建每个单元格Cell，即列的数据
        if (Utils.isNotEmpty(row)) {
            for (int i = 0; i < row.size(); i ++) {
                XSSFCell xssfCell = xssfRow.createCell(i); //创建单元格
                String str = row.get(i);
                if(str==null) {
                    str = "";
                }
                //https://blog.csdn.net/weixin_29100399/article/details/114203311
                if(str.indexOf("function::") != -1) {
                    xssfCell.setCellFormula(str.substring(10,str.length())); //设置单元格内容
                } else if(changeNum.indexOf(","+i+",")!=-1) {
                    xssfCell.setCellType(CellType.NUMERIC);
                    if(Utils.isNotEmpty(row.get(i))) {
                        xssfCell.setCellValue(Double.valueOf(row.get(i))); //设置单元格内容
                    } else {
                        xssfCell.setCellValue(0);
                    }
                } else {
                    xssfCell.setCellValue(row.get(i)); //设置单元格内容
                }
                if (cellStyle != null) {
                    xssfCell.setCellStyle(cellStyle);
                }
            }
        }
    }

    public void setRowNoValue(XSSFRow xssfRow,List<String> row, XSSFCellStyle cellStyle, String changeNum) {
        //创建每个单元格Cell，即列的数据
        if (Utils.isNotEmpty(row)) {
            for (int i = 0; i < row.size(); i ++) {
                XSSFCell xssfCell = xssfRow.createCell(i); //创建单元格
                if (cellStyle != null) {
                    xssfCell.setCellStyle(cellStyle);
                }
            }
        }
    }

   /* private void setBillTotalFee_S_F(Bill bill2, User user) {
        BillFee billFee = new BillFee();
        billFee.setBillId(bill2.getId());
        List<BillFee> billFees = billFeeBiz.list(billFee, user);
        float tst = 0,tsd = 0, tft = 0,tfd = 0,calculate = 0;

        List<BillFeeVo> list = new ArrayList<>();
        BillFeeVo vo = new BillFeeVo();

        for (BillFee bf : billFees) {
            if(Utils.isNotEmpty(bf.getPrice())){
                calculate = Float.parseFloat(bf.getPrice());
                vo = new BillFeeVo();
                vo.setType(bf.getType());
                vo.setCurrency(bf.getCurrency());
                vo.setTotal(calculate+"");
                if(bf.getType().equals("应收")){
                    tst += calculate;
                }
                if(bf.getType().equals("应付")){
                    tft += calculate;
                }
                if(Utils.isNotEmpty(bf.getExchangeRate())&&Utils.isNotEmpty(bf.getInvoicemum())) {
                    calculate = Float.parseFloat(bf.getPrice())*Float.parseFloat(bf.getExchangeRate());
                    vo.setTotal(calculate+"");
                    if(bf.getInvoicestate().equals("1")) {
                        calculate = calculate*Float.parseFloat(bf.getInvoicemum());
                        vo.setTotalNoTax(calculate+"");
                    } else {
                        vo.setTotalNoTax("0");
                    }
                    if(bf.getType().equals("应收")){
                        tsd += calculate;
                    }
                    if(bf.getType().equals("应付")){
                        tfd += calculate;
                    }
                }
            }
        }
        Bill b = new Bill();
        b.setId(billFee.getBillId());
        b.setFeeTotalF(tsd+"");
        b.setFeeTotalS(tfd+"");
        billBiz.updateTotalFee_S_F(b, user);
    }

    //司机费用车队审核
    @Override
    @Transactional
    public JsonResult driverFeeFleetVerify(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
        BillProcess bp2 = new BillProcess();
        bp2.setBillId(bill2.getId());
        List<BillProcess> bpList = billProcessBiz.list(bp2);
        boolean feeRet = false;
        if(bpList!=null && bpList.size()>2) {
            for (BillProcess bp3 : bpList) {
                if (bp3.getState().equals("司机费用车队已审核")) {
                    return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
                }
                if (bp3.getState().equals("货代费用已审核")) {
                    feeRet = true;
                }
            }
        } else {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        if(!bill2.getState().equals("发起费用清单") && !bill2.getState().equals("货代费用已审核")
                && !bill2.getState().equals("货代费用已提交") && !bill2.getState().equals("货代费用未通过")) {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
//        if(feeRet) {
//            bill.setState("结单");
//        } else {
//            bill.setState("司机费用车队已审核");
//        }
        bill.setState("司机费用车队已审核");
        if (billBiz.driverFeeVerify(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("司机费用车队已审核");
            billProcessBiz.save(bp, loginUser);
//            if(feeRet) {
//                bp.setState("结单");
//                billProcessBiz.save(bp, loginUser);
//            }

            //设置总应收应付的金额
            setBillTotalFee_S_F(bill2, loginUser);
            return JsonResult.ok("司机费用车队审核成功");
        }
        return JsonResult.error("司机费用车队审核失败");

    }

    //司机费用平台审核
    @Override
    @Transactional
    public JsonResult driverFeePlatVerify(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
        BillProcess bp2 = new BillProcess();
        bp2.setBillId(bill2.getId());
        List<BillProcess> bpList = billProcessBiz.list(bp2);
        boolean feeRet = false;
        if(bpList!=null && bpList.size()>2) {
            for (BillProcess bp3 : bpList) {
                if (bp3.getState().equals("司机费用平台已审核")) {
                    return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
                }
                if (bp3.getState().equals("货代费用已审核")) {
                    feeRet = true;
                }
            }
        } else {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        if(!bill2.getState().equals("发起费用清单") && !bill2.getState().equals("货代费用已审核")
                && !bill2.getState().equals("货代费用已提交") && !bill2.getState().equals("货代费用未通过")
                && !bill2.getState().equals("司机费用车队已审核")) {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        if(feeRet) {
            bill.setState("结单");
        } else {
            bill.setState("司机费用平台已审核");
        }
        if (billBiz.driverFeeVerify(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("司机费用平台已审核");
            billProcessBiz.save(bp, loginUser);
            if(feeRet) {
                bp.setState("结单");
                billProcessBiz.save(bp, loginUser);
            }

            //设置总应收应付的金额
            setBillTotalFee_S_F(bill2, loginUser);
            return JsonResult.ok("司机费用平台审核成功");
        }
        return JsonResult.error("司机费用平台审核失败");
    }

    //货代费用提交
    @Override
    @Transactional
    public JsonResult verify(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
        BillProcess bp2 = new BillProcess();
        bp2.setBillId(bill2.getId());
        List<BillProcess> bpList = billProcessBiz.list(bp2);
        int count = 0;
        boolean feeRet = false;
        if(bpList!=null && bpList.size()>2){
            BillProcess bp3 = bpList.get(0);
            boolean driverFee = false;
            if (bp3.getState().equals("司机费用平台已审核")) {
                BillProcess bp4 = bpList.get(1);
                if (bp4.getState().equals("司机费用车队已审核")) {
                    BillProcess bp5 = bpList.get(2);
                    if(!bp5.getState().equals("货代费用未通过") && !bp5.getState().equals("发起费用清单")){
                        driverFee = true;
                    }
                } else if(!bp4.getState().equals("货代费用未通过") && !bp4.getState().equals("发起费用清单")){
                    driverFee = true;
                }
            } else if (bp3.getState().equals("司机费用车队已审核")) {
                BillProcess bp5 = bpList.get(1);
                if(!bp5.getState().equals("货代费用未通过") && !bp5.getState().equals("发起费用清单")){
                    driverFee = true;
                }
            } else if(!bp3.getState().equals("货代费用未通过") && !bp3.getState().equals("发起费用清单")){
                driverFee = true;
            }
            if(driverFee) {
                return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
            }
            for (BillProcess bp5 : bpList) {
                if (bp5.getState().equals("司机费用平台已审核")) {
                    feeRet = true;
                }
                if (bp5.getState().equals("货代费用已提交")) {
                    count ++;
                }
            }
        } else {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        if(!bill2.getState().equals("发起费用清单")
                && !bill2.getState().equals("司机费用车队已审核")&& !bill2.getState().equals("司机费用平台已审核")
                && !bill2.getState().equals("货代费用未通过")) {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        if(count>1 && !loginUser.getRoleIds().contains(UserConstant.BILL_FEE_THIRD_ROLE_ID)){
            return JsonResult.error("第三次费用提交需要特定权限,操作失败!");
        }
        if(count>1) {
            bill.setState("货代费用已审核");
        } else {
            bill.setState("货代费用已提交");
        }
        if(count>1 && feeRet) {
            bill.setState("结单");
        }
        if (billBiz.verify(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());

            if(count>1) {
                bp.setState("货代费用已审核");
            } else {
                bp.setState("货代费用已提交");
            }
            billProcessBiz.save(bp, loginUser);
            if(count>1 && feeRet) {
                bp.setState("结单");
                billProcessBiz.save(bp, loginUser);
            }

            //设置总应收应付的金额
            setBillTotalFee_S_F(bill2, loginUser);
            return JsonResult.ok("货代费用提交成功");
        }
        return JsonResult.error("货代费用提交失败");
    }


 @Scheduled(cron="0 0 1 * * ?")
//    @Scheduled(cron="0/15 * * * * ?")
    public void billlistAtuoFinish() {
        List<Bill> billList = billBiz.listAtuoFinish();
        User user = new User();
        user.setUserId(UserConstant.ADMIN_ID);
        user.setOrganizationId(UserConstant.ORGANIZATION_ID);
        if(billList != null && billList.size() > 0) {
            for(int i = 0; i < billList.size(); i ++) {
                boolean driverFee = false;
                Bill bill = billList.get(i);
                BillProcess bp = new BillProcess();
                bp.setBillId(bill.getId());
                bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
                List<BillProcess> billProcessList = billProcessBiz.list(bp);
                boolean customer = false;
                if(billProcessList !=null && billProcessList.size()> 0) {
                    for(BillProcess bp2: billProcessList) {
                        if(bp2.getState().equals("货代费用已审核")){
                            customer = true;
                        }
                        if(bp2.getState().equals("司机费用平台已审核")){
                            driverFee = true;
                        }
                    }
                }
                if(!customer){
                    bp.setState("货代费用已审核");
                    bp.setCreateTime(DateUtil.formatDate(new Date(System.currentTimeMillis())));
                    billProcessBiz.saveWidthCreateTime(bp, user);

                    bill.setState("货代费用已审核");
                    billBiz.platReceive(bill, user);
                    if(driverFee) {
                        bp.setState("结单");
                        bp.setCreateTime(DateUtil.formatDate(new Date(System.currentTimeMillis()+1000)));
                        billProcessBiz.saveWidthCreateTime(bp, user);
                        bill.setState("结单");
                        billBiz.platReceive(bill, user);
                    }
                    //设置总应收应付的金额
                    setBillTotalFee_S_F(bill, new User(UserConstant.ADMIN_ID,UserConstant.ORGANIZATION_ID));

                    //设置费用已确认
                    BillFee billFee = new BillFee();
                    billFee.setFeeState("已确认");
                    billFee.setBillId(bill.getId());
                    billFeeBiz.updateFeeStateByBill(billFee, user);
                } else if(customer && driverFee){
                    bp.setState("结单");
                    bp.setCreateTime(DateUtil.formatDate(new Date(System.currentTimeMillis())));
                    billProcessBiz.saveWidthCreateTime(bp, user);

                    bill.setState("结单");
                    billBiz.platReceive(bill, user);
                    //设置总应收应付的金额
                    setBillTotalFee_S_F(bill, new User(UserConstant.ADMIN_ID,UserConstant.ORGANIZATION_ID));

                    //设置费用已确认
                    BillFee billFee = new BillFee();
                    billFee.setFeeState("已确认");
                    billFee.setBillId(bill.getId());
                    billFeeBiz.updateFeeStateByBill(billFee, user);
                }
            }
        }
    }

    //货代确认
    @Override
    @Transactional
    public JsonResult customerVerify(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
        if(!bill2.getCreateUser().equals(loginUser.getUserId()+"")){
            return JsonResult.error("只有下单帐号才能确认费用!");
        }
        BillProcess bp2 = new BillProcess();
        bp2.setBillId(bill2.getId());
        List<BillProcess> bpList = billProcessBiz.list(bp2);
        if(!bill2.getState().equals("货代费用已提交") && !bill2.getState().equals("司机费用已审核")
                && !bill2.getState().equals("司机费用平台已审核")
        ) {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        boolean feeRet = false;
        if(bpList!=null && bpList.size()>2) {
            BillProcess bp3 = bpList.get(0);
            boolean driverFee = false;
            if (bp3.getState().equals("司机费用平台已审核")) {
                BillProcess bp4 = bpList.get(1);
                if (bp4.getState().equals("司机费用车队已审核")) {
                    BillProcess bp5 = bpList.get(2);
                    if(!bp5.getState().equals("货代费用已提交")){
                        driverFee = true;
                    }
                } else if(!bp4.getState().equals("货代费用已提交")){
                    driverFee = true;
                }
            } else if (bp3.getState().equals("司机费用车队已审核")) {
                BillProcess bp5 = bpList.get(1);
                if(!bp5.getState().equals("货代费用已提交")){
                    driverFee = true;
                }
            } else if(!bp3.getState().equals("货代费用已提交")){
                driverFee = true;
            }
            if(driverFee) {
                return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
            }

            for (BillProcess bp5 : bpList) {
                if (bp5.getState().equals("司机费用平台已审核")) {
                    feeRet = true;
                }
            }
        } else {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        if(feeRet){
            bill.setState("结单");
        } else {
            bill.setState("货代费用已审核");
        }
        if (billBiz.customerVerify(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("货代费用已审核");
            billProcessBiz.save(bp, loginUser);
            if(feeRet) {
                bp.setState("结单");
                billProcessBiz.save(bp, loginUser);
            }
            return JsonResult.ok("货代费用审核成功");
        }
        return JsonResult.error("货代费用审核失败");
    }

    //货代驳回
    @Override
    @Transactional
    public JsonResult customerDisagree(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
        BillProcess bp2 = new BillProcess();
        bp2.setBillId(bill2.getId());
        List<BillProcess> bpList = billProcessBiz.list(bp2);
        if(!bill2.getState().equals("货代费用已提交") && !bill2.getState().equals("司机费用已审核")) {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        if(bpList!=null && bpList.size()>2) {

            BillProcess bp3 = bpList.get(0);
            boolean driverFee = false;
            if (bp3.getState().equals("司机费用平台已审核")) {
                BillProcess bp4 = bpList.get(1);
                if (bp4.getState().equals("司机费用车队已审核")) {
                    BillProcess bp5 = bpList.get(2);
                    if(!bp5.getState().equals("货代费用已提交")){
                        driverFee = true;
                    }
                } else if(!bp4.getState().equals("货代费用已提交")){
                    driverFee = true;
                }
            } else if (bp3.getState().equals("司机费用车队已审核")) {
                BillProcess bp5 = bpList.get(1);
                if(!bp5.getState().equals("货代费用已提交")){
                    driverFee = true;
                }
            } else if(!bp3.getState().equals("货代费用已提交")){
                driverFee = true;
            }
            if(driverFee) {
                return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
            }
        } else {
            return JsonResult.error("当前状态为"+ bill2.getState() + ",操作失败!");
        }
        bill.setState("货代费用未通过");
        if (billBiz.platReceive(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("货代费用未通过");
            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("货代费用未通过");
        }
        return JsonResult.error("货代费用未通过失败");
    }*/

    //订单取消
    @Override
    @Transactional
    public JsonResult billCancel(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
//        if(loginUser.getUserId() != UserConstant.ADMIN_ID){
//            if(!(bill2.getState().equals("已下单") || bill2.getState().equals("已做单"))) {
//                return JsonResult.error("当前状态为"+ bill2.getState() + ",不能取消订单!");
//            }
//        }
        if (billBiz.remove(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("取消订单");
            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("取消订单成功");
        }
        return JsonResult.error("取消订单失败");
    }

    //订单取消
    @Override
    @Transactional
    public JsonResult billCancelCustomer(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
        if(loginUser.getUserId() != UserConstant.ADMIN_ID){
            if(!(bill2.getState().equals("已下单") || bill2.getState().equals("已做单"))) {
                return JsonResult.error("当前状态为"+ bill2.getState() + ",不能取消订单!");
            }
        }
        if (billBiz.remove(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("取消订单");
            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("取消订单成功");
        }
        return JsonResult.error("取消订单失败");
    }

    //订单取消
    @Override
    @Transactional
    public JsonResult billRecover(Bill bill, User loginUser) {
        if (billBiz.billRecover(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("恢复订单");
            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("恢复订单成功");
        }
        return JsonResult.error("恢复订单失败");
    }

    //应收
    @Override
    @Transactional
    public JsonResult receivable(Bill bill, User loginUser) {
        if(bill.getId()==null||bill.getFeeStateS().equals("")) {
            return JsonResult.error("操作失败,请联系平台管理员!");
        }
        if (billBiz.feeStateS(bill, loginUser)) {
//            BillProcess bp = new BillProcess();
//            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
//            bp.setBillId(bill.getId());
//            bp.setState(bill.getState());
//            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("确认成功");
        }
        return JsonResult.error("确认失败");
    }

    //应付
    @Override
    @Transactional
    public JsonResult payable(Bill bill, User loginUser) {
        if(bill.getId()==null) {
            return JsonResult.error("操作失败,请联系平台管理员!");
        }
        if (billBiz.feeStateF(bill, loginUser)) {
//            BillProcess bp = new BillProcess();
//            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
//            bp.setBillId(bill.getId());
//            bp.setState(bill.getState());
//            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("确认成功");
        }
        return JsonResult.error("确认失败");
    }

    @Override
    public JsonResult save(Bill bill, User loginUser) {
        if (billBiz.save(bill, loginUser)) {
            return JsonResult.ok("添加成功");
        }
        return JsonResult.error("添加失败");
    }

    @Transactional
    @Override
    public JsonResult update(Bill bill, User loginUser) {
        Bill bill2 = billBiz.getById(bill);
        if(!loginUser.getRoleIds().contains(UserConstant.BILL_UPDATE_ROLE_ID)){
            if(!bill2.getState().equals("已下单")) {
                return JsonResult.error("当前状态为"+ bill2.getState() + ",不能修改订单!");
            }
        }
        if (StrUtil.hasBlank( bill.getId(),
                bill.getCustomer(), bill.getCustomerId(),
                bill.getDock(), bill.getPackDate(),
                bill.getWeight(), bill.getBoxType(),
                bill.getVessel(), bill.getVoyage(),
                bill.getBillOfLading())
//                || bill.getAddressFactoryList()==null ||bill.getAddressFactoryList().size()==0
        ) {
            return JsonResult.error("请完整填写修改内容,操作失败!");
        }
        if(!bill2.getState().equals("已下单")&&!bill2.getState().equals("平台已接单")){
            bill.setUpdated("1");
        } else {
            bill.setUpdated("0");
        }
        if (billBiz.update(bill, loginUser)) {
            if(Utils.isNotEmpty(bill.getAddressFactoryList())){
                for (AddressFactory a : bill.getAddressFactoryList()) {
                    if(Utils.isNotEmpty(a.getId())) {
                        addressFactoryBiz.update(a, loginUser);
                    } else {
                        addressFactoryBiz.save(a, loginUser);
                    }
                }
            }

            bill.setBillId(bill.getId());
            bill.setFactoryAddressJson(JSONArray.fromObject(bill.getAddressFactoryList()).toString());
            billExamineService.save(bill, loginUser);
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult updateFinance(Bill bill, User loginUser) {
        if (billBiz.updateFinance(bill, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult updateRemark(Bill bill, User loginUser) {
        if (billBiz.updateRemark(bill, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult updateBoxNumPic(Bill bill, User loginUser) {
        if (StrUtil.hasBlank(bill.getId())) {
            return JsonResult.error("请完整填写id,操作失败!");
        }
        if (billBiz.updateBoxNumPic(bill, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult updateSealnoPic(Bill bill, User loginUser) {
        if (StrUtil.hasBlank(bill.getId())) {
            return JsonResult.error("请完整填写id,操作失败!");
        }
        if (billBiz.updateSealnoPic(bill, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult updatePreEntryPic(Bill bill, User loginUser) {
        if (StrUtil.hasBlank(bill.getId())) {
            return JsonResult.error("请完整填写id,操作失败!");
        }
        if (billBiz.updatePreEntryPic(bill, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult updateAnnex(Bill bill, User loginUser) {
        if (StrUtil.hasBlank(bill.getId())) {
            return JsonResult.error("请完整填写id,操作失败!");
        }
        if (billBiz.updateAnnex(bill, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult updateSfile(Bill bill, User loginUser) {
        if (StrUtil.hasBlank(bill.getId())) {
            return JsonResult.error("请完整填写id,操作失败!");
        }
        if (billBiz.updateSfile(bill, loginUser)) {
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult changeDropBox(Bill bill, User loginUser) {
        if (StrUtil.hasBlank(bill.getId())) {
            return JsonResult.error("请完整填写id,操作失败!");
        }
//        if(!loginUser.getRoleIds().contains(UserConstant.DISPATCH_ROLE_ID)){
//            return JsonResult.error("只能调度师傅修改落箱状态,操作失败!");
//        }
        if (billBiz.changeDropBox(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("落箱");
            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult changeWithholdingBox(Bill bill, User loginUser) {
        if (StrUtil.hasBlank(bill.getId())) {
            return JsonResult.error("请完整填写id,操作失败!");
        }
//        if(!loginUser.getRoleIds().contains(UserConstant.DISPATCH_ROLE_ID)){
//            return JsonResult.error("只能调度师傅修改预提状态,操作失败!");
//        }
        if (billBiz.changeWithholdingBox(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("预提");
            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult changeCash(Bill bill, User loginUser) {
        if (StrUtil.hasBlank(bill.getId())) {
            return JsonResult.error("请完整填写id,操作失败!");
        }
//        if(!loginUser.getRoleIds().contains(UserConstant.DISPATCH_ROLE_ID)){
//            return JsonResult.error("只能调度师傅修改预提状态,操作失败!");
//        }
        if (billBiz.changeCash(bill, loginUser)) {
            BillProcess bp = new BillProcess();
            bp.setOrganizationId(UserConstant.ORGANIZATION_ID+"");
            bp.setBillId(bill.getId());
            bp.setState("现金");
            billProcessBiz.save(bp, loginUser);
            return JsonResult.ok("修改成功");
        }
        return JsonResult.error("修改失败");
    }

    @Override
    public JsonResult remove(Bill bill, User loginUser) {
        if (billBiz.remove(bill, loginUser)) {
            return JsonResult.ok("删除成功");
        }
        return JsonResult.error("删除失败");
    }

    @Override
    public JsonResult writeOffAccountsList(PageResult2<T> page, WriteOff bill, User loginUser) throws Exception {
        JsonResult jr = billBiz.writeOffAccountsList(page,bill,loginUser);
        return jr;
    }

}
